Use tablesample to limit the result set [reprinted]

This article Reprinted from: http://msdn.microsoft.com/zh-cn/library/ms189108.aspx

 

Tablesample is a new syntax introduced from SQL 2005. It can sample table data.

 

A. Select the row percentage

Person. ContactThe table contains 19,972 rows. The following statement returns approximately10%. The number of rows returned for each execution of this statement is usually different.

 
Use adventureworks; goselect firstname, lastnamefrom person. Contact tablesample (10 percent );
 

B. Select the percentage of rows with a seed value

Each execution, the following statements return the same group of rows. Seed Value205Is optional.

 
Use adventureworks; goselect firstname, lastnamefrom person. Contact tablesample (10 percent) repeatable (205 );
 

C. select several rows

The following statement returns approximately100Line. The actual number of returned rows may vary greatly. If a small value is specified, for example, 5, no result can be received in the example.

 
Use adventureworks; goselect firstname, lastnamefrom person. Contact tablesample (100 rows );
 

Note that the tablesample clause is different from the top clause, and the top clause is sequential. Tablesample is a random sample.
 

 
The result set of tablesample may be uncertain. Either.
 

 

If you want to get a fixed number of random rows, you may need to consider other methods.

For example

Select top (10) * From northwind .. orders order by newid ()