Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?

I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.

From: sqlite-users <> on behalf of 
Hick Gunter <>
Sent: Monday, January 8, 2018 1:19:00 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 

>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <>
>Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = 
>However, I’m still confused. Reading this 
>suggests the table is stored in RowID order. So what happens if I insert a 
>>record into Tbl with a lower ID than the existing 2.4 million Ids?

IMHO you should NEVER set the rowid (INTEGER PRIMARY KEY) field yourself, only 
copy it's value to the foreign key field of a referring row and within a 
transaction. Using the internal rowid in a foreign key for double lookup speed 
is the intended use.

SQLite "usually" uses 1 more than the highest rowid currently in use; if the 
maximum possible rowid is used, it will select a random rowid, hoping to find a 
free rowid within a limited number of attempts.

SQLite does allow you to modify/set the rowid, but this will fail if the target 
rowid is already in use. If you require custom rowids, these should be kept in 
separate fields. You can still use the SQLite rowid in foreign keys.

 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
sqlite-users mailing list
sqlite-users mailing list

Reply via email to