Ben Forta

AdobeStock_455007340

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 */
SELECT *
FROM table
TABLESAMPLE (5 ROWS)
/* Get a sample 10 percent of rows */
SELECT *
FROM table
TABLESAMPLE (10 PERCENT)
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
    tof

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

    Reply
  2. intj Avatar
    intj

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

    Reply
  3. abhi Avatar
    abhi

    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!

    Reply

Leave a Reply