SQLite’s WITHOUT ROWID tables are a performance optimization that eliminates the hidden rowid column, allowing the primary key to act as the clustered index for the table.
Here’s a demonstration of how it works with a simple users table:
-- Create a table WITHOUT ROWID
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT
);
-- Insert some data
INSERT INTO users (user_id, username, email) VALUES
(101, 'alice', 'alice@example.com'),
(102, 'bob', 'bob@example.com'),
(103, 'charlie', 'charlie@example.com');
-- Query by primary key
SELECT * FROM users WHERE user_id = 102;
When you query SELECT * FROM users WHERE user_id = 102;, SQLite directly uses the user_id B-tree to locate the row. Without WITHOUT ROWID, SQLite would have an additional rowid B-tree that points to the actual data B-tree, adding an extra lookup step.
The core problem WITHOUT ROWID tables solve is the overhead of the implicit rowid column. Every table in SQLite normally has an invisible rowid column that serves as its primary key if one isn’t explicitly defined. When you define your own primary key (like user_id in the example), SQLite still maintains the rowid and uses it as an alias for your primary key in some contexts, but it also maintains a separate B-tree index for the rowid itself. This means every row has two entries in the main B-tree structure: one for your primary key and one for the rowid.
For tables with a single-column integer primary key, WITHOUT ROWID is particularly effective. It removes the rowid column entirely. The primary key you define then becomes the only index used to locate rows, effectively making it the clustered index. This means the data is physically stored in the order of your primary key.
Consider a table with a rowid:
CREATE TABLE posts (
post_id INTEGER PRIMARY KEY,
title TEXT,
content TEXT
);
INSERT INTO posts (post_id, title, content) VALUES (1, 'First Post', 'Content 1');
SQLite internally manages two B-trees for posts: one for post_id and one for rowid. If post_id is an alias for rowid (which it is here because it’s an INTEGER PRIMARY KEY), there’s a slight redundancy.
Now, compare with WITHOUT ROWID:
CREATE TABLE articles (
article_id INTEGER PRIMARY KEY,
title TEXT,
content TEXT
) WITHOUT ROWID;
INSERT INTO articles (article_id, title, content) VALUES (1, 'First Article', 'Content A');
In articles, there is only one B-tree: the one for article_id. The data for the row is stored directly within this article_id B-tree. This reduces the storage footprint (no extra rowid column and its index) and speeds up lookups because there’s one less index to traverse.
The primary levers you control are the definition of a single-column INTEGER PRIMARY KEY and the WITHOUT ROWID clause. The choice is critical for tables where you frequently query by the primary key and want to minimize storage and maximize lookup speed.
The main constraint is that WITHOUT ROWID tables must have a primary key, and that primary key must be an alias for an INTEGER or TEXT type. You cannot have a composite primary key or a primary key that isn’t an INTEGER or TEXT. Also, you cannot use INSERT OR ROLLBACK or INSERT OR IGNORE directly on WITHOUT ROWID tables; you must use INSERT and handle conflicts explicitly.
One subtle aspect is how SQLite handles updates. When you update a row in a WITHOUT ROWID table, the new data is inserted into the primary key’s B-tree, and the old row is marked for deletion. Because the primary key is the data, this operation is more direct than in a table with rowid, where the rowid index might need to be updated separately from the data itself. This makes updates, especially those involving primary key changes (though generally discouraged), potentially more efficient.
If you try to insert a duplicate primary key into a WITHOUT ROWID table, you will get a "UNIQUE constraint failed" error, and the entire transaction will be rolled back by default unless you’ve specified ON CONFLICT for the primary key or are using a transaction that handles errors differently.