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.
select top 5 *
from table
order by newid()
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!