Retrieving random data from SQL Server table

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.