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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Hick Gunter <h...@scigames.at> 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 = NULL' >-----Ursprüngliche Nachricht----- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von x >Gesendet: Montag, 08. Jänner 2018 11:39 >An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = >NULL' > >However, I’m still confused. Reading this https://sqlite.org/queryplanner.html >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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
