I didn't forget the automatic index. I even referred to it. But you are right about the need to INTEGER PRIMARY KEY to be able to use the rowid as a foreign key. This also prevents VACCUM from renumbering the records, so doing that would require extra work at the application level (and ON UPDATE CASCADE, which is beyond the scope of VACUUM). But even without renumbering, 4 byte rowids (29 usable bits) will last for nearly a billion records.
I still hold: If you "care" about the actual value of an "id" (not "rowid"), you should not be using the INTEGER PRIMARY KEY to hold it. If you "don't care" about the actual value of the rowid, then you should not be setting it -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von David Raymond Gesendet: Montag, 08. Jänner 2018 19:13 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL' Don't forget the automatic index that gets created when the primary key isn't an alias of the rowid. Also why on earth would you ever have a foreign key that references the rowid if you're not using it as the id of the record? You even mentioned "vaccum allows SQLite to renumber the rows using the smallest possible numbers" so you would never, ever want to use rowid as a foreign key field unless it was an integer primary key visible as the primary key of the table. Hmm, during a vacuum, when SQLite re-numbers the rowids, if you made a foreign key to the rowid with "on update cascade" does it in fact cascade since it's not a normal transaction? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Monday, January 08, 2018 1:02 PM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL' Lets take a simple example of an employee table and couple of requirements: x) Ids shall be non-reusable x) Ids shall be indicative of the country the employee is located in at the time of hiring x) Ids shall be assigned in a way that employee counts and seniority may not be simply deducted x) Ids shall be usable for 100 years' worth of business Looking at country numbering schemes, the UN currently uses 3 digit numbers (10 bits), using 16 bits gives some wiggle room. Looking at the largest employers, the US DoD currently employs 3.2 million, at a turnover rate of 25% for uniformed personnel, giving an estimated range of 80.000.000 (27 bits) so using 32 bits gives us some wiggle room. The Employee ID can be built as the sum of the coutry coude left shifted by 32 bits plus the country-specific serial number, giving 48 bits of data, and scrambled in a way that ensures that some of the high order bits are set, yielding 15 digit global employee ids, that SQLite stores as 7 byte integers. Declaring Create table employee( id integer primary key, ...) forces SQLite to use this 7 byte value as the rowid, not only in the employee table, but also in all the foreign keys that refer to it. Let's assume that there are a dozen relations that refer to the employee id. This gives a total of 13 * 7 = 91 bytes of storage for storing and referencing employee ids. Declaring Create table employee (id integer, ... , primary key (id)); allows SQLite to use (and reuse) the rowid. Even if you store 1 million active records (about twice the size of the US Postal Service), letting SQLite have cutody of the rowid uses only up to 3 bytes for a rowid. The total is then 7 + 12*3 = 43 bytes of storage for storing the employee id and the rowid references, which translates to over 50% savings. The smaller size of rowids in the internal btree structures should also just about cancel out the need to keep an index that translates employee ids to rowids. Purging records and then performing a vaccum allows SQLite to renumber the rows using the smallest possible numbers. So, not assigning an external meaning to the rowid (and forcing the inner workings to comply) can, at least in some cases, actually save space. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von x Gesendet: Montag, 08. Jänner 2018 16:59 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL' 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 ___________________________________________ 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 ___________________________________________ 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
