Tablesample sample data

SQL Server 2005 introduces a new feature tablesample to randomly retrieve some sample data from an existing table, which can specify the number of rows,

You can also specify the percentage. For example:
Select *
From performance. DBO. Orders tablesample (1000 rows );

 

Select *
From DBO. Orders tablesample (0.1 percent );

 

However, it is worth noting that,It does not mean that the specified number of rows is returned accurately.. Because the randomly selected unit isData page,
Therefore, if you specify to return 1000 rows, it is possible to return 800,900,120 0.
If you need to specify the exact number of rows, You can randomly select more data and then use the top keywords, such:
Select top (1, 1000 )*
From DBO. Orders tablesample (2000 rows );

To return the same sample data, useRepeatableKeyword to specify a specific seed.
The sample data returned by executing this statement multiple times is the same. For example:
Select *
From DBO. Orders tablesample (1000 rows)Repeatable(40 );

 

Tablesample detailsAlgorithm
For example, table A has 100 rows and 3 data pages with, 32, and 50 data respectively. If two rows of data are specified
Calculate the page selection probability. The algorithm is 2/100 = 1/50. It is easy to calculate that this statement does not return any rows
The probability is (1-1/50) ^ 3 = 94%. The probability of returning 18 rows is (1-1/50) * 1/50*1/50 = 3.92%. Likewise
Other values can be calculated. VisibleTablesample is not suitable for selecting a small amount of data.

To select a small amount of data, use the following method:
Select top (n )*
From DBO. Orders
Order by newid ();
Note: This method is much slower.