Using SQL Server NEWID() for Retrieving Random Rows from a Table

← PrevNext →

The NEWID() function in SQL Server returns a unique id or a random value. For example, the query SELECT NEWID() will always return a unique value (yes always). I’ll show you how using the NEWID() function, you can retrieve random rows from a table in SQL Server.

The NEWID() function, 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

See the result.

SQL Server NEWID Function

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()

Random rows with SQL Server NEWID()

If you execute the above query repeatedly, you will see a new result set (with a new order) every time.

You may also like: Convert Rows into Columns using SQL Server PIVOT

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.

Also Read: How to use the LEFT() and RIGHT() functions in SQL Server

Conclusion

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.

Thanks for reading .

← PreviousNext →