Retrieving random data from SQL Server with TABLESAMPLE

Problem

In a previous tip we talked about how to randomly retrieve records from the database.  This tip took a look at both the RAND() function and the NEWID() function.  Both of these approaches are helpful, but one problem with both of these is that depending on how you use them you may end up reading through your entire table to get a random distribution of data.  The good part about this is that you are pretty much guaranteed that you will get a totally random set of records each time.  Because of the potential overhead with reading through the entire table are there any other approaches to randomly retrieving data?

Solution

In SQL Server there is an option that can be added to the FROM clause, this option is the TABLESAMPLE feature.

With the TAMPLESAMPLE option you are able to get a sample set of data from your table without having to read through the entire table or having to assign temporary random values to each row of data.  At first glance this sounds great, but there are a few things to keep in mind when using this new option.

The way this works is that data is read at a page level.  Each 8K page for the table is given a random value and based on this random value and the value you specify either number of rows or percentage will determine how many rows are returned.  So based on this, each run can retrieve a totally different number of rows of data.

How to use it

To use TABLESAMPLE, this clause is put after the FROM clause as follows:

• …FROM tableName TABLESAMPLE (10 PERCENT)
• …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.

Here is a sample query against the AdventureWorks database.

SELECT * FROM Sales.SalesOrderDetail TABLESAMPLE (1000 ROWS)

Here are a few sample runs using the above statement:

As you can see none of these executions returned 1000 rows.  The total number of rows is pretty random.  To help make this return an exact number of rows you can use the TOP command as well such as:

SELECT TOP 250 * FROM Sales.SalesOrderDetail TABLESAMPLE (1000 ROWS)

By using the TOP command with a smaller number than the sample rows we are pretty much guaranteed to get the number of rows we are expecting from the query.

Another thing to keep in mind is that if you make the ROWS value too small there is a chance that you can get no data returned from the query.  In my tests when I set this value to (100 ROWS) some of the executions returned no data at all.  So keep this in mind.

Another thing to be aware of is that the data is pulled page by page, not row by row.  So based on this, your sampling dataset will be as diverse as the data is that is stored on each data page. So based on our example, the clustered index is on the SalesOrderID.  This value is not all that random across each page of data and therefore we have groups of data that are random versus a complete random set of data at a row level.

Here is another query to further illustrate this.

SELECT TOP 10 * FROM Sales.SalesOrderDetail TABLESAMPLE (1000 ROWS)

As you can see from the five executions below the SalesOrderID is the same for all of the executions except for run #4.  So since the data is stored based on the SalesOrderID a sample of only 10 records will not be all that random.

In addition to randomly retrieving data you can all use the REPEATABLE option so that the query returns the same random set of data each time you run the query.  Again this assumes that your data has not changed.

SELECT TOP 10 * FROM Sales.SalesOrderDetail TABLESAMPLE (1000 ROWS) REPEATABLE (25)

When the above query is run the sample set of data that is returned will be the same each time the query is executed.

Next Steps

• Take a look at this other tip that discusses randomly pulling data: SQL Server Randomly Retrieve Records
• Read more about the TABLESAMPLE option