What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012
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.
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.