Documentation Source Text

<tcl>hd_fragment searching strategies</tcl>
<h2>1.0 Searching</h2>

<h3>1.1 Tables Without Indices</h3>

<p>
Every table in SQLite consists of zero or more rows with a unique integer
key (the [rowid] or [INTEGER PRIMARY KEY]) followed by content.  The rows


are logically stored in order of increasing rowid.  As an example, this
article uses a table named "FruitsForSale" which relates various fruits 
to the state
where they are grown and their unit price at market.  The schema is this:
</p>

<tcl>code {
................................................................................
<li>Output rows can be returned to the application as each small sort
    completes, and well before the table scan is complete.
<li>If a LIMIT clause is present, it might be possible to avoid scanning
    the entire table.
</ol>
Because of these advantages, SQLite always tries to do a partial sort using an
index even if a complete sort by index is not possible.</p>

<tcl>hd_fragment searching strategies</tcl>
<h2>1.0 Searching</h2>

<h3>1.1 Tables Without Indices</h3>

<p>
Most tables in SQLite consist of zero or more rows with a unique integer
key (the [rowid] or [INTEGER PRIMARY KEY]) followed by content.  
(The exception is [WITHOUT ROWID] tables.)
The rows
are logically stored in order of increasing rowid.  As an example, this
article uses a table named "FruitsForSale" which relates various fruits 
to the state
where they are grown and their unit price at market.  The schema is this:
</p>

<tcl>code {
................................................................................
<li>Output rows can be returned to the application as each small sort
    completes, and well before the table scan is complete.
<li>If a LIMIT clause is present, it might be possible to avoid scanning
    the entire table.
</ol>
Because of these advantages, SQLite always tries to do a partial sort using an
index even if a complete sort by index is not possible.</p>

<h2>4.0 WITHOUT ROWID tables</h2>

<p>
The basic principals described above apply to both ordinary rowid tables
and [WITHOUT ROWID] tables.
The only difference is that the rowid column that serves as the key for
tables and that appears as the right-most term in indexes is replaced by
the PRIMARY KEY.
</p>

<h3>1.2 Compatibility</h3>

<p>SQLite [version 3.8.2] or later is necessary in order to use a WITHOUT
ROWID table.  An attempt to open a database that contains one or more WITHOUT
ROWID tables using an earlier version of SQLite will result in a
"malformed database schema" error.</p>

















<h2>2.0 Differences From Ordinary Rowid Tables</h2>

<p>The WITHOUT ROWID syntax is an optimization.  It provides no new
capabilities.  Anything that can be done using a WITHOUT ROWID table
can also be done in exactly the same way, and exactly the same syntax,
using an ordinary rowid table.  The only advantage of a WITHOUT ROWID
table is that it can sometimes use less disk space and/or perform a little

<h3>1.2 Compatibility</h3>

<p>SQLite [version 3.8.2] or later is necessary in order to use a WITHOUT
ROWID table.  An attempt to open a database that contains one or more WITHOUT
ROWID tables using an earlier version of SQLite will result in a
"malformed database schema" error.</p>

<h3>1.3 Quirks</h3>

<p>WITHOUT ROWID is found only in SQLite and is not compatible
with any other SQL database engine, as far as we know.
In an elegant system, all tables would behave as WITHOUT ROWID
tables even without the WITHOUT ROWID keyword.  However, when SQLite was
first designed, it used only integer [rowid|rowids] for row keys 
to simplify the implementation.
This approach worked well for many years.  But as the demands on
SQLite grew, the need for tables in which the PRIMARY KEY really did
correspond to the underlying row key grew more acute.  The WITHOUT ROWID
concept was added
in order to meet that need without breaking backwards
compatibility with the billions of SQLite databases already in use at
the time (circa 2013).

<h2>2.0 Differences From Ordinary Rowid Tables</h2>

<p>The WITHOUT ROWID syntax is an optimization.  It provides no new
capabilities.  Anything that can be done using a WITHOUT ROWID table
can also be done in exactly the same way, and exactly the same syntax,
using an ordinary rowid table.  The only advantage of a WITHOUT ROWID
table is that it can sometimes use less disk space and/or perform a little