Documentation Source Text

or index.  There is a one table b-trees in the database file
for each rowid table in the database schema, including system tables
such as sqlite_master.  There is one index b-trees
in the database file for each index in the schema, including implied indices
created by uniqueness constraints.  There are no b-trees associated with
[virtual tables].  Specific virtual table implementations might make use
of [shadow tables] for storage, but those shadow tables will have separate
entries in the database schema.  [WITHOUT ROWID] tables use an index b-tree
as their princpal storage rather than a table b-tree, so there is one
index b-tree in the database file for each [WITHOUT ROWID] table.
The b-tree corresponding to the sqlite_master table is always a table
b-tree and always has a root page of 1.
The sqlite_master table contains the root page number for every other 
table and index in the database file.</p>

<p>Each entry in a table b-tree consists of a 64-bit signed integer key

or index.  There is a one table b-trees in the database file
for each rowid table in the database schema, including system tables
such as sqlite_master.  There is one index b-trees
in the database file for each index in the schema, including implied indices
created by uniqueness constraints.  There are no b-trees associated with
[virtual tables].  Specific virtual table implementations might make use
of [shadow tables] for storage, but those shadow tables will have separate
entries in the database schema.  [WITHOUT ROWID] tables use index b-trees
rather than a table b-trees, so there is one
index b-tree in the database file for each [WITHOUT ROWID] table.
The b-tree corresponding to the sqlite_master table is always a table
b-tree and always has a root page of 1.
The sqlite_master table contains the root page number for every other 
table and index in the database file.</p>

<p>Each entry in a table b-tree consists of a 64-bit signed integer key

but traditional rowid tables tend to work faster for large row sizes.
This is because rowid tables are implemented as [B*-Trees] where
all content is stored in the leaves of the tree, whereas WITHOUT ROWID 
tables are implemented using ordinary B-Trees with content stored on both
leaves and intermediate nodes.  Storing content in 
intermediate nodes mean that each intermediate node entry takes up more
space on the page and thus reduces the fan-out, increasing the search cost.







<p>Note that except for a few corner-case differences detailed above,
WITHOUT ROWID tables and rowid tables work the same.  They both generate
the same answers given the same SQL statements.  So it is a simple matter
to run experiments on an application, late in the development cycle,
to test whether or not the use of WITHOUT ROWID tables will be helpful.
A good strategy is to simply not worry about WITHOUT ROWID until near
the end of product development, then go back and run tests to see
if adding WITHOUT ROWID to tables with non-integer PRIMARY KEYs helps
or hurts performance, and retaining the WITHOUT ROWID only in those cases
where it helps.

but traditional rowid tables tend to work faster for large row sizes.
This is because rowid tables are implemented as [B*-Trees] where
all content is stored in the leaves of the tree, whereas WITHOUT ROWID 
tables are implemented using ordinary B-Trees with content stored on both
leaves and intermediate nodes.  Storing content in 
intermediate nodes mean that each intermediate node entry takes up more
space on the page and thus reduces the fan-out, increasing the search cost.

<p>The "sqlite3_analyzer.exe" utility program, available as source code
in the SQLite source tree or as a precompiled binary on the
[http://www.sqlite.org/download.html | SQLite Download page], can be
used to measure the average sizes of table rows in an existing SQLite
database.</p>

<p>Note that except for a few corner-case differences detailed above,
WITHOUT ROWID tables and rowid tables work the same.  They both generate
the same answers given the same SQL statements.  So it is a simple matter
to run experiments on an application, late in the development cycle,
to test whether or not the use of WITHOUT ROWID tables will be helpful.
A good strategy is to simply not worry about WITHOUT ROWID until near
the end of product development, then go back and run tests to see
if adding WITHOUT ROWID to tables with non-integer PRIMARY KEYs helps
or hurts performance, and retaining the WITHOUT ROWID only in those cases
where it helps.