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 thoughts

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

  2. 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 to tof Cancel reply