Thursday, March 29, 2018

TableSample in FROM clause to limit number of rows

TableSample clause started with SQL Server 2015 limits the number of rows returned from a table to a sample % or sample numbers.

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 a suprprisining results:

I queried the Northwind databases Orders table using the above syntax.  The 829 rows table yielded the following. These are results of actual queries run in SQL Server 2016 Developers edition.


 Select * From Orders---829 rows

 Select * From Orders TableSample (10 PERCENT)

83 Rows

42 rows

43 rows

162 rows


 Select * From Orders TableSample (10)

210 rows

163 rows

 Select * From Orders TableSample (10 rows)

0 Rows

43 rows

81 rows

If you are using this in the FROM Clause, look out for surprises.





No comments:

Post a Comment

What is SQLOISIM? What is it used for in SQL Server?

SQLIOSIM is a tool for simulating SQL Server IO. SQLIOSIM performs reliability and integrity tests on the disk systems that SQL Server uti...