By: Greg Robidoux | Comments (2) | Related: > Functions System
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:
| Run # | # of Rows |
|---|---|
| 1 | 1288 |
| 2 | 658 |
| 3 | 806 |
| 4 | 1232 |
| 5 | 1064 |
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.
| SalesOrderID returned | ||||
|---|---|---|---|---|
| Run #1 | Run #2 | Run #3 | Run #4 | Run #5 |
| 47967 47967 47967 47967 47967 47967 47967 47967 47967 47967 |
50208 50208 50208 50208 50208 50208 50208 50208 50208 50208 |
43850 43850 43850 43850 43850 43850 43850 43850 43850 43850 |
44311 44311 44312 44312 44312 44312 44313 44313 44313 44313 |
44127 44127 44127 44127 44127 44127 44127 44127 44127 44127 |
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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
