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)

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 (http://blog.SQLAuthority.com) , BOL

About these ads

17 thoughts on “SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

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

  2. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

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

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

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

  7. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31 « Journey to SQLAuthority

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

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s