SQLite: ROWID and VACUUM

A. ROWID
Every row of every SQLite table has a 64-bit signed integer key that is unique within the same table. This integer is called "rowid". The rowid is the actual key used in the B-Tree that implements an SQLite table. Because rows are stored in rowid order, a rowid is a candidate for a primary key, as it can uniquely identify a record within a table...

Below we see how it works:

1. WITHOUT Primary Key Keyword
sqlite>CREATE TABLE t1 (name TEXT);
sqlite>insert into t1 values ('a');
sqlite>insert into t1 values ('b');
sqlite>insert into t1 values ('c');
sqlite>insert into t1 values ('d');
sqlite>select rowid, name from t1;
rowid name
1 a
2 b
3 c
4 d

sqlite>delete from t1 where name='a';
sqlite>delete from t1 where rowid=3;
sqlite>select rowid, name from t1;
rowid name
2 b
4 d
sqlite>vacuum;
sqlte>select _rowid_, name from t1;
rowid name
1 b
2 d

2. WITH Primary Key Keyword
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, name TEXT);

insert into t2(name) values ('a');
insert into t2(name) values ('b');
insert into t2(name) values ('c');
insert into t2(name) values ('d');

select rowid, pk, name from t2;
pk pk_1 name
1 1 a
2 2 b
3 3 c
4 4 d

delete from t2 where name='a';
delete from t2 where rowid=3;
select oid, pk, name from t2;
pk pk_1 name
2 2 b
4 4 d

sqllite>vacuum;
select oid, pk, name from t2;
pk pk_1 name
2 2 b
4 4 d

B. OBSERVED
- As we saw, the ROWID in the second sample will be retain static!
- So, don't forget to include INTEGER PRIMARY KEY in your Create Table statement.
- Rowid is integer key with amount of 9223372036854775807. Is it enough for you?.
- The rowid value can be accessed using one of the special names "ROWID", "OID", or "_ROWID_".
- If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a "real" database column but instead becomes an alias for the rowid. Unlike normal SQLite columns, the rowid must be a non-NULL integer value.
- The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.

C. VACUUM
When an object (table, record, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.