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: |
3710449fcd5be6975ddd68ce1a4c27e2 |
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
Changes to pages/queryplanner.in.
︙ | ︙ | |||
47 48 49 50 51 52 53 | <tcl>hd_fragment searching strategies</tcl> <h2>1.0 Searching</h2> <h3>1.1 Tables Without Indices</h3> <p> | | | > > | 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 |
︙ | ︙ |