Thursday, March 29, 2018

How does TableSample in FROM clause of a SELECT Query work?

TableSample clause started with SQL Server 2015 limits the number of rows returned from a table to a sample % or sample numbers. In fact, it may not provide the specified rows of %. Also, it is not for tables with too few rows.

Here is how TableSample is defined in the MSDN site:
------------
TABLESAMPLE (10 PERCENT) /*Return a sample 10 percent of the rows of the result set. */
TABLESAMPLE (15 ROWS) /* Return a sample of 15 rows from the result set. */.
--------------
For these scenarios it cannot be applied:

Derived tables
Linked Server Tables
Tables from Table-Valued functions
Row-set functions
Open XML

This here is the syntax for TableSample clause:
----------------
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]

TableSample in FROM Clause does not behave as defined in the syntax and could provide surprising results:

I queried the Northwind databases Orders table using the following syntax in SQL Server 2016 Developers edition.

SELECT * FROM Orders TableSample(10 Percent)
SELECT * FROM Orders TableSample (10)--surprisingly this does not result in error
SELECT * FROM Orders TableSample (10 ROWS)

These queries were run a number of times and the rows returned were variable from run to run and some times resulted in 0 returned rows.

No comments: