Random Rows in SQL 2005

I blogged awhile ago about selecting random rows in SQL ... the best way (IMHO) to do so is still the ol':

SELECT * FROM Foo ORDER BY newid()

... And of course you can set the number of rows to return in the usual way.

In SQL 2005, you can use the TABLESAMPLE clause to perform similar functions -- either returning a percentage or specified number of rows.  For example:

SELECT * FROM Foo FROM TABLESAMPLE SYSTEM (2 PERCENT)
SELECT * FROM Foo FROM TABLESAMPLE SYSTEM (100 ROWS)

You can also specify the REPEATABLE option and provide a seed to get the same rows (of course, this assumes there are no changes to the table between queries):

SELECT * FROM Foo FROM TABLESAMPLE SYSTEM (100 ROWS) REPEATABLE (123456)

Now, many folks have noted that the sample size doesn't always return the exact number of rows requested.  Here's why:

TABLESAMPLE SYSTEM returns an approximate percentage of rows. It generates a random value for each physical 8 KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is included in the sample or excluded. Each page that is included returns all rows in the sample result set. For example, when specifying TABLESAMPLE SYSTEM 10 PERCENT, SQL Server returns all the rows on approximately 10 percent of the specified table's data pages. If the rows are evenly distributed on the pages of the table, and if there is a sufficient number of pages in the table, the number of rows returned should approximate the sample size requested. However, as the random value generated for each page is independent of the values generated for any other page, it is possible that a larger, or smaller, percentage of pages than requested are returned. The TOP(n) operator can be used to limit the number of rows to a given maximum.

Cool stuff -- I haven't had a need for this yet, but you never know when it will come in handy.
Comments are closed

My Apps

Dark Skies Astrophotography Journal Vol 1 Explore The Moon
Mars Explorer Moons of Jupiter Messier Object Explorer
Brew Finder Earthquake Explorer Venus Explorer  

My Worldmap

Month List