SQLite Forum

Without rowid
Login

Without rowid

(1) By Tim Streater (Clothears) on 2020-09-12 16:35:12 [link] [source]

In an idle moment today, I happened to be looking at the WITHOUT ROWID docs, and seeing an example of when one might use it in a database I have, did the following. The original schema was this, in the BEFORE database:

create table info (token text primary key, ct1 integer, ct2 integer, ct3 integer); CREATE TABLE counts ( xtotal integer not null default 0, ytotal integer not null default 0 ); CREATE TABLE globals (app_version integer, data_version integer default 0);

I then created an AFTER database with the same schema, except that the info table was defined thus:

create table info (token text primary key, ct1 integer, ct2 integer, ct3 integer) without rowid;

I then copied the data from BEFORE to AFTER as follows, using the CLI:

sqlite3 AFTER SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. sqlite> attach BEFORE as sp; sqlite> insert into main.info select * from sp.info; sqlite> insert into main.counts select * from sp.counts; sqlite> insert into main.globals select * from sp.globals; sqlite> detach database sp; sqlite3> ctrl-d

I then exchanged the filenames so that my app would use the new database. Note that info has circa 170k rows, and the other two tables one row each. BEFORE is about 6.95Mbytes, AFTER is about 3.87Mbytes. So neither is large.

Is there any reason that BEFORE and AFTER might not be functionally identical?

First time I ran my app using AFTER, it appeared to go into a loop and I got "database locked" when trying to look at AFTER with the CLI. It appears happier now.

(2) By Larry Brasfield (LarryBrasfield) on 2020-09-12 16:54:12 in reply to 1 [source]

Is there any reason that BEFORE and AFTER might not be functionally identical?

Yes, and the reason is that an extra index is required for your BEFORE DB.

For the BEFORE DB: sqlite> .header on sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|info|info|2|CREATE TABLE info (token text primary key, ct1 integer, ct2 integer, ct3 integer) index|sqlite_autoindex_info_1|info|3| table|counts|counts|4|CREATE TABLE counts ( xtotal integer not null default 0, ytotal integer not null default 0 ) table|globals|globals|5|CREATE TABLE globals (app_version integer, data_version integer default 0) sqlite> , and for the AFTER DB: sqlite> .header on sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|info|info|2|CREATE TABLE info (token text primary key, ct1 integer, ct2 integer, ct3 integer) without rowid table|counts|counts|3|CREATE TABLE counts ( xtotal integer not null default 0, ytotal integer not null default 0 ) table|globals|globals|4|CREATE TABLE globals (app_version integer, data_version integer default 0) sqlite>

You have demonstrated why the "without rowid" feature was added.

(3) By Tim Streater (Clothears) on 2020-09-12 17:27:03 in reply to 2 [link] [source]

But if I've read the docs correctly, that's an internal matter which should mean, in my use case, a slight speedup and reduction in file size. Anything else?

(4.1) By Larry Brasfield (LarryBrasfield) on 2020-09-12 20:30:54 edited from 4.0 in reply to 3 [link] [source]

You asked if they "might not be functionally identical". The speedup and file size impacts can be considered functional attributes. Disregarding those, and ignoring the possibility that somebody might consider the return from select last_insert_rowid(); or queries like select * from info where rowid > 0; to be "functional", the two DBs should produce the same response to queries except for uncontrolled result set ordering.