7

My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n)

So I wrote the following queries to just check (as I'm learning T-SQL)

SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person TABLESAMPLE(10)

SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person TABLESAMPLE(10 PERCENT)

Each query returns a different number of rows every time I execute it.

So I obviously don't understand TABLESAMPLE. Could somebody explain?

0

TableSample() does not guarantee returning the number or % you specify. I found that it can return 0 rows, less than what you requested and more than what you specified.

I ran this query on the Orders table in Northwind which has 829 rows.

select * from orders TableSample(10 percent)

In the different times I ran this query, I got, 124, 42,82, 85,0, 0, 84. ..

4

The short answer is that TABLESAMPLE is not guaranteed to return the number of rows you specify (or the %) because it bases this on pages and not on rows, and returns all of the rows from the pages it identifies. Unless your table only has fixed width columns, the pages pulled out based on % or number of rows could contain vastly different numbers of rows.

You can make this more consistent using the REPEATABLE option, but that still won't make it return the right number of rows. If you are simply trying to retrieve an exact number or percentage of random rows, use:

SELECT TOP (10) -- or 10 PERCENT
  columns
FROM dbo.table
ORDER BY NEWID();

Please read through this tip and this question and all the answers.

3

I agree with @Aaron but I should mention if you want truly random records this may be most proper specially in huge tables:

SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

Please read the full description in MSDN

Hope this help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.