Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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