Documentation Source Text

Check-in [3710449fcd]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional information about WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3710449fcd5be6975ddd68ce1a4c27e24c9b0b42
User & Date: drh 2015-08-12 04:23:55.669
Context
2015-08-19
13:55
Fix the argvIndex description in the virtual table documentation. (check-in: 26089f7cd9 user: drh tags: trunk)
2015-08-12
04:23
Additional information about WITHOUT ROWID tables. (check-in: 3710449fcd user: drh tags: trunk)
2015-08-11
16:38
Refinements to the undo/redo technical note. (check-in: 1bf74f2849 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/queryplanner.in.
47
48
49
50
51
52
53
54
55


56
57
58
59
60
61
62

<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 {







|
|
>
>







47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

<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 {
719
720
721
722
723
724
725










<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>

















>
>
>
>
>
>
>
>
>
>
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
<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>
Changes to pages/withoutrowid.in.
38
39
40
41
42
43
44
















45
46
47
48
49
50
51
<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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<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