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

While going through the articles in this blog, you must have noticed a section below every post, which shows a list of Related Articles. It shows a minimum of 10 articles that relates to the contents of a particular article. I wanted a method where I could retrieve random rows from a database table for every page refresh. This way it would rotate and show different articles.

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

Output

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

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.

Related: 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.

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.



Related: SQL Server LEFT() and RIGHT() functions

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 numbers etc. Let me know if you have figured out another useful query using this function.

Thanks for reading.

Previous - Convert Rows into Columns using SQL Server PIVOT | How do we use PIVOT in SQL Server?Next - How to Convert Month Number in a Date to Month Name in SQL Server



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

s