SQL Server: TableSample clause

A quick way to get a random sampling of data from a table is to use the TableSample clause. Below I am going to show you some different ways to run queries using this clause.

Here is a count of the number of rows in one of my tables, it has 961,542 rows.

Below are the columns in the table, pretty standard looking.

Below I add the TABLESAMPLE clause and what percentage of rows of the 961,542 I want returned. I am asking for 10% of the rows, and it returns a random listing of rows. You can see it is random by looking at the rowid column, the numbers jump around.

Here is another query, where I am just counting the number of rows while asking for 10% of the rows. However, don’t let the “percent” fool you. That percentage
is the percentage of the table’s data pages. Once the sample pages are selected, all rows for the selected pages are returned. Since the fill state of pages can vary, the number of rows returned will also vary—you’ll notice this in the row count returned.

Here is one that looks like it brought less than 9%.

You can also sample by number of rows.

If you designate the number of rows, this is actually converted by SQL Server into a
percentage, and then the same method used by SQL Server to identify the percentage of data pages is used.