Monday, 19 July 2010

How to Select Random Records using Transact-SQL

You may need to select some random records from a table, having done this using T-SQL, seems awesome!
It is very simple, e.g. for selecting ten random records:
SELECT TOP 10 * FROM tablename ORDER BY NEWID()
It just works fine!

Not let's see how it works:

The query except the ORDER BY clause, is a very simple select query, the only thing that makes it different, is the ORDER BY clause. We used NEWID() function as order clause. So let's continue our discussion with this function. What does NEWID() do?
It creates a unique value of type uniqueidentifier. To see it in action, execute the following query:
SELECT NEWID()
Each time you execute the above query, you get a unique value of type uniqueidentifier.
Now we come back to our first query (which was for selecting some random records). It works as follows: It selects some records, and for each record, it generates a new unique value, then it sorts the result based on the values of generated unique identifier values. So it looks like you have some randomly selected records! That's all.