What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

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?

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.

StackExchange Database Administrators Q#48615, answer score: 6