The TABLESAMPLE returns random rows from a table. It can be used to get a sample of rows from a table. It doesn’t return exactly the same percentage of rows specified.
|
SELECT * FROM Person.Person
TABLESAMPLE (10 PERCENT)
|
It will return to approx. 10% rows (not exact)
See the example
See some runs
|
Select
(SELECT COUNT(*) FROM Person.Person) As Total_Records,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As First_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Second_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Third_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Fourth_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Fivth_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Sixth_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Seventh_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) ) As Eighth_Run
|
Use of the REPEATABLE Option
The REPEATABLE option causes a selected sample to be returned again. When REPEATABLE is specified with the same repeat_seed value, SQL Server returns the same subset of rows, as long as no changes have been made to the table. When REPEATABLE is specified with a different repeat_seed value, SQL Server will typically return a different sample of the rows in the table. The following actions to the table are considered changes: inserting, updating, deleting, index rebuilding, and index defragmenting, restoring a database, and attaching a database.
See the example
|
Select
(SELECT COUNT(*) FROM Person.Person) As Total_Records,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As First_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Second_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Third_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Fourth_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Fivth_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Sixth_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Seventh_Run,
(SELECT COUNT(*) FROM Person.Person TABLESAMPLE (10 PERCENT) REPEATABLE(1)) As Eighth_Run
|
It 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.
