sqlite-without-rowid · shipping · also WITHOUT ROWID table

SQLite WITHOUT ROWID table

A SQLite WITHOUT ROWID table stores its rows in an index b-tree keyed by its declared primary key instead of a separate integer rowid.

Related Observatory article: SQLite WITHOUT ROWID table on hesela.com

Every such table needs a primary key, and INTEGER PRIMARY KEY does not become an alias for rowid. AUTOINCREMENT and incremental BLOB I/O are unavailable for this table kind. It is a schema choice, not a universal speed or space improvement. [1]

The row's columns are encoded in the index b-tree key; unlike ordinary table b-trees, index b-trees can hold record payload in internal cells as well as leaves. [2] In 12 generated fixtures, all 512 rows were recovered while leaf-only cell counts omitted records held internally; the observations are not a workload benchmark. [3]

Secondary indexes carry primary-key columns instead of rowid. A primary-key column already indexed with the same collation is not repeated in the suffix; a different collation can require another occurrence. [2]

Relations

related

SQLite rowid, B-tree, B+ tree

Citations

1. SQLite: Clustered Indexes and the WITHOUT ROWID Optimization (accessed 2026-10-22)
2. SQLite database file format: b-tree pages and WITHOUT ROWID representation (accessed 2026-10-22)
3. Hesela: 12 controlled SQLite b-tree layout observations, source identity and limits (accessed 2026-10-22)

Further reading

4. Hesela: B-epsilon trees, B-tree foundations and observed page placement (accessed 2026-10-22)

JSON: /data/terms/sqlite-without-rowid.json