TABLESAMPLE

Query Techniques
Buy Me A Coffee

Return a random sample of rows from a table efficiently, without scanning and sorting the full table.

Returns a statistical sample of rows from a table using BERNOULLI (row-level random) or SYSTEM (block-level random) methods. Useful for quick approximate counts and sampling large tables. The sample size is approximate, not exact.

Compatibility

Filter by Database
SQL TABLESAMPLE Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Not Supported - Not supported. ORDER BY RAND() LIMIT n works but scans the full table and is very slow on large datasets.
PostgreSQL Native 9.5 Two methods: BERNOULLI (true row-level statistical sample — accurate but slower) and SYSTEM (page-level — faster but may have clustering bias). Optional REPEATABLE(seed) for reproducible results. Custom sampling methods can be added via extensions (e.g., tsm_system_rows).
SQL Server Native 2005 TABLESAMPLE SYSTEM(n PERCENT) or TABLESAMPLE (n ROWS). Only the SYSTEM method (page-level). Optional REPEATABLE(seed).
Oracle Native 8i Non-standard SAMPLE clause syntax (not called TABLESAMPLE). SAMPLE(pct) is row-level (like BERNOULLI); SAMPLE BLOCK(pct) is page-level (like SYSTEM). Optional SEED(n) for reproducibility.
SQLite Not Supported - Not supported. Workaround: random rowid selection.

Details

PostgreSQL is the only engine offering both BERNOULLI (true row-level) and SYSTEM (page-level) methods in standard SQL. SYSTEM is faster but results can be biased toward rows that happen to share a page. Oracle uses non-standard SAMPLE syntax.

Standard Syntax

SELECT * FROM tbl TABLESAMPLE BERNOULLI(10); -- ~10% of rows, row-level SELECT * FROM tbl TABLESAMPLE SYSTEM(10); -- ~10% of rows, page-level (faster)

Version Support

MySQL: Not supported PostgreSQL: Native since 9.5 SQL Server: Native since 2005 Oracle: Native since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL Alternative syntax

Not supported. ORDER BY RAND() LIMIT n works but scans the full table and is very slow on large datasets.

SELECT * FROM tbl ORDER BY RAND() LIMIT 1000; -- slow SELECT * FROM tbl WHERE id % 10 = 0 LIMIT 1000; -- faster but deterministic/biased

PostgreSQL Native syntax

Two methods: BERNOULLI (true row-level statistical sample — accurate but slower) and SYSTEM (page-level — faster but may have clustering bias). Optional REPEATABLE(seed) for reproducible results. Custom sampling methods can be added via extensions (e.g., tsm_system_rows).

SELECT * FROM tbl TABLESAMPLE BERNOULLI(10); SELECT * FROM tbl TABLESAMPLE SYSTEM(10); SELECT * FROM tbl TABLESAMPLE BERNOULLI(5) REPEATABLE(42); -- reproducible -- With extension: SELECT * FROM tbl TABLESAMPLE SYSTEM_ROWS(1000); -- exact row count

SQL Server Native syntax

TABLESAMPLE SYSTEM(n PERCENT) or TABLESAMPLE (n ROWS). Only the SYSTEM method (page-level). Optional REPEATABLE(seed).

SELECT * FROM tbl TABLESAMPLE SYSTEM(10 PERCENT); SELECT * FROM tbl TABLESAMPLE (1000 ROWS) REPEATABLE(42);

Oracle Native syntax

Non-standard SAMPLE clause syntax (not called TABLESAMPLE). SAMPLE(pct) is row-level (like BERNOULLI); SAMPLE BLOCK(pct) is page-level (like SYSTEM). Optional SEED(n) for reproducibility.

SELECT * FROM tbl SAMPLE(10); -- row-level ~10% SELECT * FROM tbl SAMPLE BLOCK(10); -- page-level ~10% SELECT * FROM tbl SAMPLE(10) SEED(42);

SQLite Alternative syntax

Not supported. Workaround: random rowid selection.

SELECT * FROM tbl WHERE rowid IN ( SELECT rowid FROM tbl ORDER BY RANDOM() LIMIT 1000 );