Enterprise only
Use TABLESAMPLE to explore a subset of a table or estimate aggregate
statistics. The sampling method determines how much data is read and whether
the table’s physical layout influences the sample.
ROWS for a count instead.
Rows or fragments
The standard calls these two methods of one clause. They are different operations, and the difference matters on Lance:BERNOULLI gives a genuinely uniform sample and reads across the whole table to
get it. SYSTEM reads far less (it skips entire fragments), but the sample is
only as uniform as the data’s layout. If rows were written in an order that
correlates with what you are measuring, a fragment sample inherits that
correlation.
Use SYSTEM for a quick look at the data and BERNOULLI when you need
independent row sampling. Omitting the method gives
BERNOULLI.
Repeatability
Without a seed, every execution resamples: running the same query twice gives different rows.REPEATABLE fixes the draw:
Where the clause can go
It attaches to a table inFROM, including inside a subquery, a CTE, or either
side of a join:
Unsupported sampling forms
Sampling is semantics, not a hint. A clause that were read and then not applied would be a wrong answer rather than a missing feature, so anything that cannot be honoured exactly is an error:TABLESAMPLE is also refused on a WAL-backed table, where the rows a sample
would draw from are not all in the fragments yet.