Introduced in SQL Server 2005, TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.
Example 1:
SELECT FirstName,LastName
FROM Person.Contact
TABLESAMPLE SYSTEM (10 PERCENT)
Example 2:
SELECT FirstName,LastName
FROM Person.Contact
TABLESAMPLE SYSTEM (1000 ROWS)
If you run above script many times you will notice that different numbers of rows are returned everytime. Without any data changing, re-running the identical query keeps giving different results. This is non -deterministic factor of TABLESAMEPLE clause. If table is static and rows are not changed what could be the reason to return different numbers of the rows to return in each execution. The factor is 10 PERCENT is not the percentages of the table rows or tables records, it is percentages of the table’s data pages. Once the sample pages of data selected, all the rows from the selected pages are returned, it will not limit the number of rows sampled from that page. Fill factor of all the pages varies depends on the data of the table. This makes script to return different row count in result set everytime it is executed. 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. .
Following example is from BOL:
The following example returns the same set of rows every time that it is executed. The seed value of 205 was chosen arbitrarily.
USE AdventureWorks
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
13 Comments. Leave new
Thank you, this was helpful
HI ,
Im getting an error like “Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘system’.”
When i try to use run the following query.
select test from test tablesample system(10 percent)
Hi Pinal,
I think for this you can use newid() function of sql server.
Like if anyone want to select 10 rows from table in random order. He can use
select top 10 * from Table_Name order by newid()
hi sourabh,
basically your suggested newid() function gives some records always but in Tablesample case, it gives 0 or any no. of values, which are not predactable, so system tested properly, using that.
Hi Pinal,
When I copy your example and executed in Adventureworks Data base it is executed successfully But when I executed similar kind of query in my database “RND” it gives me error as it is posted by sudarsan earlier.
Query:
select empno,fname from dbo.Employees
TABLESAMPLE SYSTEM (20 PERCENT)
Error: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘SYSTEM’.
Are there any data base properties to be set to use this TABLESAMPLE ?
Please reply this comment as i could not get the solution from long time.
Regards,
GK
Hello!
BOL: When using TABLESAMPLE against databases that are upgraded to SQL Server 2005, the database’s compatibility level must be set to 90.
Check compatibility level of ADW.
Regards,
Sly
Really a very helpfull article.
Thanks alot :)
what is the purpose of ranking function pls explain sir…
can i set rows limit in a table in sql server 2005 ?
Youn use either SET ROWCOUNT or TOP Operator
Thanks Pinal It’s really usefull
The factor is 10 PERCENT is not the percentages of the table rows or tables records, it is percentages of the table’s data pages. (these lines are not in book sqlserver interview questions and answers sir, ) if have that’s wrong printed
it is interesting to 10 PERCENT is not actually 10 percent of total records instead percentage of data pages spans for this table, so it is retrieving different number of rows each time it runs..