The "NEWID()" function, when used with an order by clause, will return a set of random rows from a table.
Let's 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
See the result.
The output, as you can see, is in ascending order, which is a default behavior.
Now, let’s add the NEWID() function with the "order by" clause. This would return the rows in random order. 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.
Here is the query to get rows in rondom order.
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 and unique values etc. Let me know if you have figured out another useful query using this function.