By default, every row in SQLite has a special column called rowid. If the phrase
WITHOUT ROWID is added to the end of a
CREATE TABLE statement, then the special rowid column is dropped. That might give some
space and performance advantages of omitting that column.
What is its use?
One advantage obviously is to save some space for tables created with
WITHOUT ROWID phrase.
Another important optimization gained is search performance will improve especially for tables that have non-integer or composite (multi-column) PRIMARY KEYS and that do not store large strings or BLOBs.
Before understanding why, we need to know SQLite internal implementations of rowid tables and
WITHOUT ROWID tables: traditional
rowid tables are implmented as B*-Trees whereas
WITHOUT ROWID tables are using ordinary B-Trees. B*-Trees store all content
in the leaves while B-Tree store content on both leaves and intermediate nodes. That means
WITHOUT ROWID tables might take up more disk
space than its rowid equivalent and thus reduces the fan-out, increasing the search cost (search depth increased).
Also to keep in mind is,
PRIMARY KEY in SQLite, including
INT PRIMARY KEY, is really just a
UNIQUE index in an ordinary rowid
INTEGER PRIMARY KEY column in rowid table is an alias for the rowid and therefore is a true primary key. This indicates
INTEGER PRIMARY KEY rowid table saves more space in intermediate nodes (compared to
WITHOUT ROWID equivalent) AND has primary key
also as the key to locate rows instead of needing another index tree to locate them.
In other words,
WITHOUT ROWID optimization will only work under certain circumstances:
- For a single
INTEGER PRIMARY KEYtable,
WITHOUT ROWIDphrase will not out-perform the ordinary rowid tables: do NOT use
INTEGER PRIMARY KEYtables.
- For non-integer or composite primary key tables, if the primary key column(s) store large strings or BLOBs, this technique is more likely to loose its advantages due to the underlying B-Tree implementation. In other words, use it on non-integer or composite primary SMALL-SIZED key(s) tables.