Random sampling

Applies to: Embedded ❌, Enterprise ✅ 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.

SELECT * FROM documents TABLESAMPLE (10)        -- 10 percent
SELECT * FROM documents TABLESAMPLE (500 ROWS)  -- 500 rows

A bare number is a percentage. Add 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:

SELECT * FROM documents TABLESAMPLE BERNOULLI (10)
SELECT * FROM documents TABLESAMPLE SYSTEM (10)

Method	Aliases	What it picks
`BERNOULLI`	`ROW`	rows, each considered independently
`SYSTEM`	`BLOCK`	Lance fragments, and takes whatever rows they hold

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:

SELECT * FROM documents TABLESAMPLE (10) REPEATABLE (42)

Use the same seed and table snapshot to reproduce a sampling run. For a fixed evaluation set, save the selected row IDs or materialize the sample and retain its version. A seed alone does not preserve membership after source changes or changes to the physical layout.

Where the clause can go

It attaches to a table in FROM, including inside a subquery, a CTE, or either side of a join:

SELECT * FROM (SELECT * FROM documents TABLESAMPLE (10)) d

SELECT * FROM a TABLESAMPLE (5 ROWS) JOIN b ON a.id = b.id

SELECT * FROM t a TABLESAMPLE (10) JOIN t b TABLESAMPLE (10) ON a.id = b.id

Each side of a join samples independently. Sampling both sides of a join shrinks the result far more than either fraction suggests, because a row only survives if both its sides were drawn.

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 (101)`	a percentage outside `0..=100` is refused, not clamped
`TABLESAMPLE (1.5 ROWS)`	a row count must be whole
`TABLESAMPLE (BUCKET 1 OUT OF 4)`	bucket sampling is not supported
`(SELECT ...) d TABLESAMPLE (10)`	a subquery’s result cannot be sampled; sample the table inside it
the `SAMPLE` spelling	use `TABLESAMPLE`
the `OFFSET` modifier	not supported

TABLESAMPLE is also refused on a WAL-backed table, where the rows a sample would draw from are not all in the fragments yet.

Sampling outside SQL

For a training loop rather than a query, the SDKs read a table in a shuffled order without going through SQL at all. See Training, where a permutation drives the data loader.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Random sampling

Rows or fragments

Repeatability

Where the clause can go

Unsupported sampling forms

Sampling outside SQL

​Rows or fragments

​Repeatability

​Where the clause can go

​Unsupported sampling forms

​Sampling outside SQL

Rows or fragments

Repeatability

Where the clause can go

Unsupported sampling forms

Sampling outside SQL