I am not a guru in database programming. Therefore, it took me some time to figure out a solution and finally I came across an SQL Server built-in function called the NEWID(). The NEWID() in SQL Server, when used with an order by clause, will return a set of random rows from a table.
Let us see an example. I have a table called “books” and it has few rows of data in it. Here is the dummy books table I have mentioned.
A simple SELECT query would return all the rows in ascending order.
SELECT *FROM dbo.books
The output, as you can see, is in ascending order, which is a default behavior.
Now, let’s add the NEWID() function along with the order by clause. This would return the rows randomly. Keep your eyes on the BookID column in the output.
SELECT *FROM dbo.books ORDER BY NEWID()
If you execute the above query repeatedly, you will see a new result set (with a new order) every time.
Get Top 10 Rows from a Table, Randomly
Here’s another situation. I have hundreds of rows in a table and I wish to retrieve only TOP 10 rows from the table, randomly, out of the many rows.
Note: You need to add more rows to the table to see the desired result.
However, here is the query.
SELECT TOP 10 * FROM dbo.Books ORDER BY NEWID()
A Word of Caution
The NEWID() function executes on each row and in the process generates unique GUID’s (Globally Unique Identifier) against each row in the memory. Therefore, the execution process slows down depending upon the number of rows in the table. This may take a hit on the performance of the query. Therefore, apply this function on small tables for great results.
This article with its example will give you an idea about how to get or retrieve random rows from a table. It is simple, though the SQL Server built-in function NEWID() has other uses too, such as generating random numbers etc. Let me know if you have figured out another useful query using this function.
Thanks for reading.