SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

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)

Solarwinds

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

Solarwinds

, ,
Previous Post
SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types
Next Post
SQL SERVER – Download Feature Pack for Microsoft SQL Server 2005

Related Posts

13 Comments. Leave new

  • Thank you, this was helpful

    Reply
  • 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)

    Reply
  • Sourabh Sachdeva
    September 3, 2009 4:36 pm

    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()

    Reply
    • Ashish Kadam
      July 21, 2011 7:13 pm

      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.

      Reply
  • 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

    Reply
  • 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

    Reply
  • Really a very helpfull article.
    Thanks alot :)

    Reply
  • what is the purpose of ranking function pls explain sir…

    Reply
  • can i set rows limit in a table in sql server 2005 ?

    Reply
  • Thanks Pinal It’s really usefull

    Reply
  • 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

    Reply
  • vikramkmahapatra
    June 5, 2013 9:43 am

    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..

    Reply

Leave a Reply

Menu