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)
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 TableSample (10 PERCENT)
Select * From Orders TableSample (10 rows)
If you are using this in the FROM Clause, look out for surprises.
No comments:
Post a Comment