TABLESAMPLE Samples Pages, Not Rows: Sampling a Large Table Right

One percent of a large table sounds like a job for TABLESAMPLE, but it selects data pages rather than independent rows. Rows stored together arrive together, the count varies, and a small table can return nothing. Compare it with row-level random methods before calling the sample representative.

See What TABLESAMPLE Actually Requests

TABLESAMPLE (1 PERCENT) asks SQL Server for an approximate fraction of pages. Asking for 10000 ROWS also uses page sampling to approximate the requested row count; my test table returned a little over 10,000. A small table with only a few pages can produce zero rows even when one percent of its rows would be nonzero. In my tests, a table of a few pages returned nothing at 1 PERCENT, while a one-page table returned every row.

I ask whether the analysis needs speed, an exact sample size, or equal chance for each row. Those are different goals. If a clustered table groups recent orders together, a page sample can pull many neighbors from one period and few from another.

Count a TABLESAMPLE Page Sample

Use a real large table and run the sample several times. The example uses dbo.FactSales as a placeholder. Record sample row count and logical reads under SET STATISTICS IO. A count that changes between runs is normal; it shows why a fixed report denominator should not assume exactly one percent.

SET STATISTICS IO ON;
SELECT COUNT_BIG(*) AS sampled_rows
FROM dbo.FactSales TABLESAMPLE (1 PERCENT);
SELECT COUNT_BIG(*) AS sampled_rows
FROM dbo.FactSales TABLESAMPLE (1 PERCENT);
SET STATISTICS IO OFF;

For a small table, repeat the test and expect empty results or the whole table. The physical page layout, compression, and partitioning affect how many rows sit on selected pages. A page sample can be appropriate for a quick rough profile of a very large object, but label the result as approximate.

Compare a NEWID Sort

ORDER BY NEWID() assigns a random GUID to rows and sorts them, then TOP chooses a requested count. It can give an exact count when the table has enough rows, but it reads and sorts the input and can be expensive. Use a fixed count derived from the full table row count when a near-one-percent comparison is needed.

DECLARE @total bigint = (SELECT COUNT_BIG(*) FROM dbo.FactSales);
DECLARE @take bigint = CEILING(@total * 0.01);
SELECT TOP (@take) SalesID, SaleDate, Amount
FROM dbo.FactSales
ORDER BY NEWID();

This is a benchmark baseline, not a recommendation for every production report. Sorting millions of rows can spill to tempdb and consume a large memory grant. Capture the actual plan and elapsed time. If the table changes between count and SELECT, even the intended fraction can shift.

Compare a CHECKSUM Filter

CHECKSUM(NEWID(), SalesID) gives a varying integer per row. A modulo filter accepts roughly one in one hundred rows, so it is a row-level approximate sample. It still scans candidate rows and has minor modulo bias; it does not guarantee an exact count. Cast to bigint before ABS to avoid the minimum-int overflow case. Keep the column inside CHECKSUM. With CHECKSUM(NEWID()) alone, SQL Server 2025 turned the test into a startup filter evaluated once per query, and a large test table returned zero rows every time.

SELECT SalesID, SaleDate, Amount
FROM dbo.FactSales
WHERE ABS(CONVERT(bigint,CHECKSUM(NEWID(), SalesID))) % 100 = 0;

The filter is non-deterministic, so each run returns a different set. It is useful for quick test data or exploratory analysis when approximate size is acceptable. For statistical or financial sampling, document the selection method and evaluate whether its properties meet the review requirement.

Check Spread, Not Only Count

If SalesID roughly follows insertion order, group sampled rows into key ranges and compare how many ranges are represented. Page sampling can cluster rows from the same physical area. The query below groups one page-sample result; repeat with the row filter and compare occupied bins and the shape of counts.

WITH sample_rows AS
(
    SELECT SalesID
    FROM dbo.FactSales TABLESAMPLE (1 PERCENT)
)
SELECT SalesID / 10000 AS id_bin,
       COUNT_BIG(*) AS sampled_rows
FROM sample_rows
GROUP BY SalesID / 10000
ORDER BY id_bin;

An ID range is only a proxy for physical or business distribution. If the table is partitioned by date, compare date bands or another relevant dimension too. A sample can have the expected total count yet omit a rare category. For a decision, validate the dimensions that matter to that decision.

Repeat a TABLESAMPLE Page Choice With REPEATABLE

REPEATABLE (seed) asks SQL Server to reuse its page selection under stable table conditions. It is helpful for a repeatable rough investigation on the same data and page layout. Inserts, deletes, rebuilds, or other physical changes can change which pages exist and which rows are returned. It is not a durable list of sampled row IDs.

SELECT SalesID, SaleDate
FROM dbo.FactSales TABLESAMPLE (1 PERCENT) REPEATABLE (42);

Save the sampled keys if a later audit must examine exactly the same rows. I use REPEATABLE for a temporary diagnostic, then persist the key list for any consequential review. The seed helps repeat a page choice; it does not turn page sampling into independent row sampling.

Match the Method to the Question

TABLESAMPLE is attractive when speed matters and page clustering is acceptable. A CHECKSUM filter offers approximate row-level selection but scans rows. ORDER BY NEWID() gives a fixed count at potentially high sort cost. None of these choices replaces a documented statistical design for a high-stakes sample.

I compare count, reads, elapsed time, and spread on the same table during a quiet window. The fastest sample is useful only if it represents the data required by the analysis. State the approximation, save the selected keys when repeatability matters, and avoid calling a page sample a random set of individual rows.

Watch Physical Correlation

Page sampling is especially risky when the business variable is correlated with physical layout. A clustered index on SaleDate can place adjacent dates on nearby pages, so a sample of pages can overrepresent a few periods. A heap after a bulk load can cluster rows by loading batch. Compare the sample distribution with the full table's date bands, regions, and rare categories. A fast sample that misses the smallest customer segment can make an estimate look confident and still be wrong.

Separate Reproducibility From Accuracy

REPEATABLE makes a diagnostic easier to rerun under stable physical conditions, but it does not improve representativeness. A repeated biased sample is still biased. Save sampled primary keys when two analysts need to inspect exactly the same records. For an audit requiring a documented selection method, include the seed, table version, query, row count, and validation of the sampling frame.

I use TABLESAMPLE for rough page-level inspection and a row-level method for row-level questions. The choice is driven by the unit being sampled.

Does this page sample reflect the groups your analysis needs to compare?

Related reading on this blog: Techniques for Retrieving Random Rows and Selecting Random n Rows from a Table.

TABLESAMPLE is not a uniform row picker, it is a page sample whose spread must be tested.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.