The TABLESAMPLE clause is used to limit the number of rows returned from a table to a number or percentage of rows. Let’s look at TABLESAMPLE example.
The TABLESAMPLE returns fairly random rows from a table. It can be used to get a sample of rows from a large table, in web application where in you can’t display entire table data. It doesn’t return exactly the same percentage of rows specified. The reason is that the 10 PERCENT in above query doesn’t signify rows rather it refers to the pages. Thus, SQL Server selects 10% of the data pages and returns all the rows in the selected pages. As it’s not sure that each page will have equal number of rows, the result count differs in each execution.
The Repeatable option can be used to get the same sample in every execution. It accepts an arbitrary repeat_sead value. The sample data returned changes with different values of repeat_sead values. A query for the same is given below.
use AdventureWorks2014 GO SELECT * FROM Person.Address TABLESAMPLE(10 PERCENT) REPEATABLE(1)
Refer to http://msdn.microsoft.com/en-us/library/ms189108.aspx for more details.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
