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.

If you want to read further about the TABLESAMPLE clause please follow this link. https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s