Work@Microsoft    Study@UW.edu    Live@Seattle

SQL Script to Select n Random Rows from a Table

Rate this post

Today I need to randomly select 1000 rows from a table with over 20 million entries.  I did it using the following script.

SELECT * FROM [TableName] TABLESAMPLE (1000 ROWS)

You would think that the 1000 ROWS option would return 1000 rows, but what happens is that this number is converted to a percent prior to execution based on the number you specified and the approximate number of rows in the table.  So once again you are not guaranteed an exact number of rows.

You can also directly specify the percentage of rows to return randomly:

SELECT * FROM [TableName] TABLESAMPLE (10 PERCENT)

Leave a Comment

Your email address will not be published. Required fields are marked *

Loading...
ScottGe.net