Using TABLESAMPLE To Retrieve Partial Resultsets

I just discovered a new SELECT clause that has been added to T-SQL in SQL Server 2005. TABLESAMPLE can be used to return a sampling of rows (either a fixed number or a specified percent of the table rows). Here is how it is used:
/* Get a sample 5 rows */
FROM table
/* Get a sample 10 percent of rows */
FROM table

It is worth noting that you may not get the exact number of rows that you’d expect. Sampling occurs by table page, and the number of rows in a page can vary.

3 responses to “Using TABLESAMPLE To Retrieve Partial Resultsets”

  1. tof Avatar

    Another new function they added and I find really cool is that you can now use TOP with a DELETE.

  2. intj Avatar

    As a point of reference in SQL 2000 you can do this with the newid() function.
    select top 5 *
    from table
    order by newid()

  3. abhi Avatar

    Well, Tablesample is a great sampling method.
    But I could not get it working with table variable.
    Here is the TSQL
    declare @test table
    id int identity(1,1),
    test_count int
    insert into @test values(1)
    insert into @test values(2)
    insert into @test values(3)
    insert into @test values(4)
    insert into @test values(5)
    insert into @test values(6)
    insert into @test values(7)
    insert into @test values(8)
    insert into @test values(9)
    insert into @test values(10)
    insert into @test values(11)
    select * from @test tablesample(10)
    select * from @test tablesample(5 rows)
    select * from @test tablesample(40 percent)
    Nothing works!

Leave a Reply