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
