Monday, December 15, 2014

TableSample in SQL Server

The TABLESAMPLE clause limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. For example:
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. */.
TABLESAMPLE cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot be specified in the definition of a view or an inline table-valued function.
The syntax for the TABLESPACE clause is as follows:
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]
You can use TABLESAMPLE to quickly return a sample from a large table when either of the following conditions is true:
  • The sample does not have to be a truly random sample at the level of individual rows.
  • Rows on individual pages of the table are not correlated with other rows on the same page.

No comments:

Post a Comment