Achieve Maximum Efficiency

Intel XML

Subscribe to Intel XML: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get Intel XML: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Intel XML Authors: Jnan Dash, Jayaram Krishnaswamy, Jason Bloomberg, Chris Pollach, Peter Silva

Related Topics: Intel XML, XML Magazine

Blog Feed Post

T-SQL - TableSample does not always give the same 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. 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.

Read the original blog entry...

More Stories By Jayaram Krishnaswamy

Jayaram Krishnaswamy is a technical writer, mostly writing articles that are related to the web and databases. He is the author of SQL Server Integration Services published by Packt Publishers in the UK. His book, 'Learn SQL Server Reporting Services 2008' was also published by Packt Publishers Inc, Birmingham. 3. "Microsoft SQL Azure Enterprise Application Development" (Dec 2010) was published by Packt Publishing Inc. 4. "Microsoft Visual Studio LightSwitch Business Application Development [Paperback] "(2011) was published by Packt Publishing Inc. 5. "Learning SQL Server Reporting Services 2012 [Paperback]" (June 2013) was Published by Packt Publishing Inc. Visit his blogs at: He writes articles on several topics to many sites.