Returning random rows in a result set

March 1, 2010

Using the NEWID() function to return random rows

The NEWID function will return a GUID value, that is statistically guaranteed to be unique e.g.

If you repeatedly call this function it will return a unique GUID. You can utilise this feature to give each row a unique random value. I’ve encountered a number of occaisions where it is desiranle to return from a resultset, a random number of rows. In the past I would achieve this by returning the result set to some application code that would randomise the results and select at most N items.

The example below shows how we can return 5 random rows from the Cutsomers table (Northwind database).

Selecting 5 Random rows

Each time the SQL snippet is run, 5 random customers are returned from the table.

This is a very neat and efficient.