How SQLite chooses table types

The main table types in SQLite are ordinary tables, without ROWID tables, and temporary tables. Temporary tables are typically queries that require temporary storage of results for use. This article focuses on the differences between ordinary tables and without ROWID tables, as well as the characteristics of AutoIncrement.

What is the type of 1.rowid?

SQLite ROWID is an invisible storage column, 8 bytes of storage, it has two aliases _rowid_ and OID, the type is defined as an integer PRIMARY key, so when the user builds the table, a column is defined as an integer PRIMARY KEY, The essence is the alias of rowID. ROWID is self-increasing, when the column inserts null, the maximum value of the current table is +1, which is the value of the column. Note that the integer is different from int, such as if the column is defined as int primary key, then a null value is inserted, and the value of the column is Null,rowid is still incremented. The query can get the ROWID value from the Select rowID from TableName.

2.INTEGER PRIMARY KEY AutoIncrement and do not specify self-growing fields with ROWID difference?

1) The AutoIncrement property can only be used for columns defined as Integer PRIMARY key, otherwise an error will be created when the table is built.

2) do not use self-growing rowid always take the current maximum value of +1, if the maximum rowid is deleted, the ROWID will be reused. The use of the AutoIncrement property can avoid reuse, the system through the Sqlite_sequence table to maintain the maximum sequence value of each table, so even if there is a deletion, it will not lead to rowid reuse, strictly monotonically increment, at the cost of execution when inserting, Need to maintain the sqlite_sequence table, a certain loss of performance. Since the ROWID is stored in 8 bytes, the upper limit is 9223372036854775807, and when this value is exceeded, the rowID property randomly selects a value, as long as it does not conflict with the existing record in the table, and the AutoIncrement property prompts the error: Database or disk is full.

3) Vacuum command and ROWID

There are articles on the Internet that use vacuum command, will lead to rowid redistribution use, such as the original rowID is 1,3,5 3 records, after vacuum, will be reorganized into three-way. I personally tested it and did not reproduce it.

3.WITHOUT ROWID

1) Primray key is a unique index, table data by ROWID Organization (clustered index), through the primary key access to the table, the real need to access the unique index and clustered index, except for the integer PRIMARY key, it is an alias of rowID, The index is essentially a clustered index.

2) B-tree, B-tree, + + tree, b* tree What's the difference? The B-Tree is a two-fork search tree, B stands for binary, and the back b-,b+,b* represents balance. The B-trees discussed in the database field are noted in the following three. From the data structure, the B-Tree leaf node and non-leaf node content structure is the same, the search may find data in non-leaf nodes, directly return, while the B + tree, each search needs to find the leaf node, key information in the leaf nodes and non-leaf nodes stored two times, the leaf node contains all key information, And there are two-way pointers connected between the nodes, and the b* tree is different from the B + tree and the non-leaf nodes are connected by two-way pointers.

3) without rowid uses b-tree, leaf nodes and non-leaf nodes all have records of all content, so if the record longer (more than PAGE_SIZE*1/20), not suitable for the use of without rowid attribute, prone to frequent division of nodes. Without ROWID table has only one B-tree, access only need to access a B-tree, and the ordinary table needs to access two times (index + table), except for the integer PRIMARY key.

4) without ROWID does not support AutoIncrement property, and PRIMARY key cannot be null, normal table is perverted, PRIMARY KEY attribute column can also be null (for historical reasons, no modification).

4. How do I select a table type?

1) If the primary key is integral type, using the ordinary table, the column is defined as an integer PRIMARY KEY, so that only 1 b* tree, improve query efficiency;

2) If the primary key is non-integral type, the record is small (no more than PAGE_SIZE*1/20), and does not depend on the ROWID logic sequence number, may consider using the without ROWID table, saves the space simultaneously, enhances the query efficiency

3) In other cases, the primary key is defined using the normal table.

How SQLite chooses table types