SQLite Forum

Table Btree and Index Btree

Table Btree and Index Btree

(1) By anonymous on 2021-05-05 18:11:27 [link] [source]

I read from the documentation that there are 2 kinds of B-Tree in SQLite: Table Btree and Index Btree. My question is "When I create a table 'Create table Numbers( Number integer not null);' and then insert some numbers into it, is the data managed in an Index Btree or Table Btree and what if I use 'create index' command then? Sorry for my bad English and thanks in advance.

(2) By Warren Young (wyoung) on 2021-05-05 22:52:42 in reply to 1 [source]

The table is created using a table Btree and the index is created with an index Btree.

(3) By David Raymond (dvdraymond) on 2021-05-06 14:02:03 in reply to 1 [link] [source]

Rowid tables use table b-trees. "without rowid" tables and all indexes use index b-trees.

From the Database File Format page

A b-tree page is either a table b-tree page or an index b-tree page. All pages within each complete b-tree are of the same type: either table or index. There is one table b-trees in the database file for each rowid table in the database schema, including system tables such as sqlite_schema. There is one index b-tree in the database file for each index in the schema, including implied indexes 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_schema table is always a table b-tree and always has a root page of 1. The sqlite_schema table contains the root page number for every other table and index in the database file.