SQL SERVER – Selecting Random n Rows from a Table

Every day I spend a good amount of time with different customers helping them with SQL Server Performance Tuning issues. Today we will discuss the question asked by a developer at the organization where I was engaged in Comprehensive Database Performance Health Check. The developers wanted to know if there is any way he can randomly select n rows from a table.

This question is indeed very interesting as so far we have heard that is they is a way to retrieve TOP or BOTTOM rows but in this case, they wanted to retrieve random rows from any particular table. Additionally, they wanted to make sure that whatever the row they select ones, the same set of rows should not be selected next time and the rows should be truly random.

Honestly, it is possible to retrieve random rows from any tables. Let us see a simple example on the AdventureWorks database.

USE AdventureWorks2014
FROM [Production].[Product]

When you run the above code every single time you will see a different set of 10 rows. The trick is to add ORDER BY NEWID() to any query and SQL Server will retrieve random rows from that particular table.

I find this method very effective and would love to know your feedback about the same. Do let me know if you use any other trick and I will be happy to blog about retrieving n rows from any table randomly.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts
Next Post
SQL Server – Formatted Date and Alias Name in ORDER BY Clause

Related Posts

6 Comments. Leave new

  • Hi
    This can also work , if you can figure out how to generate a random number and pass it to the between clause then this can work well

    WITH CTE_Random
    (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS CNT, * FROM production.product )

    SELECT * FROM CTE_Random WHERE cnt BETWEEN 300 AND 600

    I tested the newid() solution on a large table , first run was 12 seconds and second run was 3 seconds

  • Carlos G Garcia
    November 4, 2018 7:49 pm

    @julian , I did test your suggestion and always get the same results not random records, is this correct ? hmmm

  • Hi Carlos
    This solution is not 100 % , you have to change the values in the between clause unless you can figure out a way to pass these values automatically, I just did not have the time to work that out

  • HI Carlos
    Try this , you can change the Rand values to what ever you want

    DECLARE @random1 int,
    @random2 int
    SET @random1 = (SELECT FLOOR(RAND()*(50-10+1))+10)
    SET @random2 = (SELECT FLOOR(RAND()*(100-10+1))+50)
    WITH CTE_Random
    (SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS CNT, * FROM production.product )

    SELECT * FROM CTE_Random WHERE cnt between @random1 and @random2

  • How do we use this query in Query Shortcuts. By selecting table name and if I click the shortcut, then it should display n randow rows.

  • I love your site! You have helped me out so much over the years!!! Thanks!


Leave a Reply Cancel reply

Exit mobile version