Clustered Indexes and the WITHOUT ROWID Optimization
Small. Fast. Reliable.<br>Choose any three.
Home<br>Menu<br>About<br>Documentation<br>Download<br>License<br>Support<br>Purchase
Search
About<br>Documentation<br>Download<br>Support<br>Purchase
Search Documentation<br>Search Changelog
Clustered Indexes and the WITHOUT ROWID Optimization
Table Of Contents<br>1. Introduction
1.1. Syntax
1.2. Compatibility
1.3. Quirks
2. Differences From Ordinary Rowid Tables
3. Benefits Of WITHOUT ROWID Tables
4. When To Use WITHOUT ROWID
5. Determining If An Existing Table Is WITHOUT ROWID
1. Introduction
By default, every row in SQLite has a special column, usually called the<br>"rowid", that uniquely identifies that row within the table. However<br>if the phrase "WITHOUT ROWID" is added to the end of a CREATE TABLE statement,<br>then the special "rowid" column is omitted. There are sometimes<br>space and performance advantages to omitting the rowid.
A WITHOUT ROWID table is a table that uses a<br>Clustered Index<br>as the primary key.
1.1. Syntax
To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID"<br>to the end of the CREATE TABLE statement. For example:
CREATE TABLE IF NOT EXISTS wordcount(<br>word TEXT PRIMARY KEY,<br>cnt INTEGER<br>) WITHOUT ROWID ;
As with all SQL syntax, the case of the keywords does not matter.<br>One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and<br>it will mean the same thing.
Every WITHOUT ROWID table must have a PRIMARY KEY. An error is raised<br>if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
In most contexts, the special "rowid" column of normal tables can<br>also be called "oid" or "_rowid_". However, only "rowid" works as<br>the keyword in the CREATE TABLE statement.
1.2. Compatibility
SQLite version 3.8.2 (2013-12-06) or later<br>is necessary in order to use a WITHOUT<br>ROWID table. An attempt to open a database that contains one or more WITHOUT<br>ROWID tables using an earlier version of SQLite will result in a<br>"malformed database schema" error.
1.3. Quirks
WITHOUT ROWID is found only in SQLite and is not compatible<br>with any other SQL database engine, as far as we know.<br>In an elegant system, all tables would behave as WITHOUT ROWID<br>tables even without the WITHOUT ROWID keyword. However, when SQLite was<br>first designed, it used only integer rowids for row keys<br>to simplify the implementation.<br>This approach worked well for many years. But as the demands on<br>SQLite grew, the need for tables in which the PRIMARY KEY really did<br>correspond to the underlying row key grew more acute. The WITHOUT ROWID<br>concept was added<br>in order to meet that need without breaking backwards<br>compatibility with the billions of SQLite databases already in use at<br>the time (circa 2013).
2. Differences From Ordinary Rowid Tables
The WITHOUT ROWID syntax is an optimization. It provides no new<br>capabilities. Anything that can be done using a WITHOUT ROWID table<br>can also be done in exactly the same way, and exactly the same syntax,<br>using an ordinary rowid table. The only advantage of a WITHOUT ROWID<br>table is that it can sometimes use less disk space and/or perform a little<br>faster than an ordinary rowid table.
For the most part, ordinary rowid tables and WITHOUT ROWID tables<br>are interchangeable. But there are some additional restrictions on<br>WITHOUT ROWID tables that do not apply to ordinary rowid tables:
Every WITHOUT ROWID table must have a PRIMARY KEY.<br>An attempt to create a WITHOUT ROWID table without a PRIMARY KEY results<br>in an error.
The special behaviors associated with "INTEGER PRIMARY KEY" do not<br>apply on WITHOUT ROWID tables.<br>In an ordinary table, "INTEGER PRIMARY KEY" means that the column is an<br>alias for the rowid. But since there is no rowid in a WITHOUT ROWID<br>table, that special meaning no longer applies. An "INTEGER PRIMARY KEY"<br>column in a WITHOUT ROWID table works<br>like an "INT PRIMARY KEY" column in an ordinary table: It is a PRIMARY KEY<br>that has integer affinity.
AUTOINCREMENT does not work on WITHOUT ROWID tables.<br>The AUTOINCREMENT mechanism assumes the presence of a rowid and so it<br>does not work on a WITHOUT ROWID table. An error is raised if the<br>"AUTOINCREMENT" keyword is used in the CREATE TABLE statement for<br>a WITHOUT ROWID table.
NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT<br>ROWID table.<br>This is in accordance with the SQL standard. Each column of a PRIMARY KEY<br>is supposed to be individually NOT NULL. However, NOT NULL was not enforced<br>on PRIMARY KEY columns by early versions of SQLite due to a bug. By the<br>time that this bug was discovered, so many SQLite databases were already<br>in circulation that the decision was made not to fix this bug for fear of<br>breaking compatibility. So, ordinary rowid tables in SQLite violate the<br>SQL standard and allow NULL values in PRIMARY KEY fields. But WITHOUT ROWID<br>tables do follow the standard and will throw an error on any attempt to<br>insert a NULL into a PRIMARY KEY column.
The sqlite3_last_insert_rowid() function<br>does not work for...