Software Technology Tips

Sometimes our table contains large number of records where we need to retrieve some of them (randomly). The TABLESAMPLE clause in SQL Server allows to extract a sampling of rows from a table in the FROM clause. It limits the number of rows returned from a table in the FORM clause to a sample number or PERCENT of rows. The TABLESAMPLE clause takes a parameter that can be a percent or a number representing how many rows to retrieve. The retrieved result of rows are random and they are not in any order. Each time you get a different result set when you run query.

For Example,

TABLESAMPLE(10 PERCENT) // Returns a sample 10 percent of the rows of the result set.

TABLESAMPLE(20 ROWS) // Returns a sample of 20 rows from the result set

Syntax:

TABLESAMPLE (sample_number[PERCENT | ROWS]).

Eg:

SELECT FIRST_NAME, LAST_NAME FROM M_USER TABLESAMPLE(10 PERCENT).

The above query returns approximately 10 percent of total rows present in that table. The number of rows returned usually changes every time that the statement is executed.

Eg:

SELECT FIRST_NAME, LAST_NAME FROM M_USER TABLESAMPLE(100 ROWS).

This query returns approximately 100 rows.