difference between 'ID IS NULL' and 'ID = NULL'

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
37 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Luuk
On 07-01-18 18:49, x wrote:
> Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it clear that Tbl had numerous secondary indexes attached to it. The table has 2.4 million records and 13 columns. There is a non-unique index on one of the other columns (x integer) which is the one appearing in my explain query plan. The following illustrates the problem. I’ve no idea why adding the 3rd column is necessary to replicate it.
>

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

>
> sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL);
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL
>
> sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER;
>
> sqlite> CREATE INDEX XXX ON TBL(X);
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL
>
> sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER;
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite>
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
>Because reading the whole record (all 3 fields) is more expensive than
>just reading the index which has all the info you need to give a correct
>answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was included in the query?


From: Luuk<mailto:[hidden email]>
Sent: 07 January 2018 18:05
To: [hidden email]<mailto:[hidden email]>
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 07-01-18 18:49, x wrote:
> Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it clear that Tbl had numerous secondary indexes attached to it. The table has 2.4 million records and 13 columns. There is a non-unique index on one of the other columns (x integer) which is the one appearing in my explain query plan. The following illustrates the problem. I’ve no idea why adding the 3rd column is necessary to replicate it.
>

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

>
> sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL);
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL
>
> sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER;
>
> sqlite> CREATE INDEX XXX ON TBL(X);
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL
>
> sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER;
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite>
>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Luuk
On 07-01-18 19:09, x wrote:
>> Because reading the whole record (all 3 fields) is more expensive than
>> just reading the index which has all the info you need to give a correct
>> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was included in the query?
>
>
sorry, i do not know
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Scott Robison-2
Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk" <[hidden email]> wrote:

> On 07-01-18 19:09, x wrote:
> >> Because reading the whole record (all 3 fields) is more expensive than
> >> just reading the index which has all the info you need to give a correct
> >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> > Yes, but the covering index has 2 fields (X & ID). The pk has only 1
> field (ID) so why not use that, particularly in the case where ‘ORDER BY
> ID’ was included in the query?
> >
> >
> sorry, i do not know
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Cezary H. Noweta
In reply to this post by curmudgeon
Hello,

To summarize:

On 2018-01-07 19:09, x wrote:
>> Because reading the whole record (all 3 fields) is more expensive than
>> just reading the index which has all the info you need to give a correct
>> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
>
> Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was included in the query?

OK - your creation statement causes that ``ID'' is an alias for
``rowid''. Why PK is not used, was mentioned by me:

On 2018-01-06 14:01, Cezary H. Noweta wrote:
> Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan.

and Scott:

On 2018-01-07 20:30, Scott Robison wrote:
> Integer primary key is by definition not null, so looking for a null value
> on an index can't work.

Look at ``where.c:whereLoopAddBtreeIndex()'':
>     if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
>      && indexColumnNotNull(pProbe, saved_nEq)
>     ){
>       continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
>     }

Even if you used ``ID INT PRIMARY...'' instead of ``ID INTEGER
PRIMARY...'' (which did not aliased ``rowid'' to ``ID'') results would
be the same, because ``ID'' is not NULL.

Now we are considering a full scan. As Luuk wrote:

On 2018-01-07 18:55, Luuk wrote:
> Because reading the whole record (all 3 fields) is more expensive than
> just reading the index which has all the info you need to give a correct
> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

Calculating a cost of a DB's searching is a bit complicated and cannot
be described in two words. You could start at a comment of
``where.c:whereLoopAddBtree()''. When your ratio of a length of an index
element to a length of a table row has been lowered, then index ``XXX''
is chosen as the most effective solution. (``rowid'' pseudo-index'
entries are considered as being equal to a whole row of a table).

However this index is used for a full scan -- not for a binary/log_2n
searching for values: ``Yahoooooo! My query is using an index so it
achieved a speed of light''. If it was true, then certainly ``XXX''
would be used earlier.

If you want to enforce some index to be used you will have to append
``INDEXED BY'' clause, however:

1. Choosing a bad index will result in no index at all.

2. As Scott noticed:

On 2018-01-07 20:30, Scott Robison wrote:
> As to why it does a table scan, the primary key isn't a separate index. The
> rowid primary key is part of the table itself.

For the purpose of ``SELECT'' there is a pseudo-index created for
``rowid'' -- it is impossible to enforce that pseudo-index. Unless you
use ``INT'' instead of ``INTEGER'' in your ``CREATE TABLE'' statement
(which will leave ``rowid'', and ``ID'' will not be alias for ``rowid'')
-- such primary index will be named ,,sqlite_autoindex_<tablename>_<n>''
-- you will have to guess <n> (or look into a db-file, or -- at best --
SELECT from ``sqlite_master'').

Primary key ``rowid'' (or alias) is not indexed, only ``primary keys''
other then ``rowid'' are. In fact it is impossible to have NULL
``rowid'' (or alias) in destroyed db -- due to a fact that it has NOT
NULL, int64 (variable length) format -- there is no place for ``I'm a
NULL'' flag. Non-rowid, NOT NULL, primary keys with NULL value could be
occurring eventually.

I hope, above will help and dispel any doubts.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
In reply to this post by Scott Robison-2
Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I imagined the RowID as being a separate index which is the root of my confusion. It would explain this



sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL ORDER BY ID;

0|0|0|SCAN TABLE TBL



contrasted with



sqlite> EXPLAIN QUERY PLAN SELECT X FROM TBL ORDER BY X;

0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX



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?





________________________________
From: sqlite-users <[hidden email]> on behalf of Scott Robison <[hidden email]>
Sent: Sunday, January 7, 2018 7:30:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk" <[hidden email]> wrote:

> On 07-01-18 19:09, x wrote:
> >> Because reading the whole record (all 3 fields) is more expensive than
> >> just reading the index which has all the info you need to give a correct
> >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> > Yes, but the covering index has 2 fields (X & ID). The pk has only 1
> field (ID) so why not use that, particularly in the case where ‘ORDER BY
> ID’ was included in the query?
> >
> >
> sorry, i do not know
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Dominique Devienne
On Mon, Jan 8, 2018 at 11:39 AM, x <[hidden email]> wrote:

> 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?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

R Smith
In reply to this post by curmudgeon
On 2018/01/08 12:39 PM, x wrote:
> Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I imagined the RowID as being a separate index which is the root of my confusion.

To elaborate a little - We often get people here asking "But why it
table-scans in stead of using my nice Index?".

This stems from an often-held misconception that Indexes are God-sent
magic to improve everything. The fact is that Indexes are costly
mechanisms which allows fast lookup which, only AFTER a certain critical
size and for specific circumstances, become more efficient than a scan.
The Query Planner has to do a lot of work to figure out what those
"critical size and specific circumstances" is for any specific query,
and it does get real fuzzy.

I think I've heard Richard or Dan explain it as follows (if memory
serves, someone please point out if I'm mistaken):

You can think of an SQLite table as essentially a btree covering Index
by itself with the Key being the Row_ID (or more recently, the PK for
WITHOUT ROWID tables). This is why the rowid (or any column serving as
an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL
values, but any other primary key could (in SQLite).

Being an Index by itself means that a Table-Scan is perhaps not as
inefficient as one might think and indeed using any other index means a
round-trip reading and hitting values in THAT index, then returning and
looking up the hit result in the rowid table index, and then reading the
pages(s) from it and extracting the data - where during a table scan,
all this round tripping is skipped.

So unless any prospective candidate Index for any query offers a truly
magnificent cost advantage, a table scan would probably be more
efficient, and so be chosen.  This is why running ANALYZE on large
tables is needed, because it allows the QP to better deduce whether a
prospective Index might in fact offer such a magnificent cost reduction
or not. Another way is hinting at the QP (Search "likelihood" in the docs).

This is why a non-rowid-alias Primary Key on a rowid table is also less
efficient to scan than the table itself (often very non-intuitive) - or
- why a covering index sometimes gets avoided in a JOIN when it seems to
contain all needed data to fulfill the join obligation.

Also, often a great index is not used simply because the query planner
does not know enough about it and its prospective cost to obtain a good
estimate of its utility, and sometimes what feels intuitively to us as a
great Index just isn't really. The QP is not infallible, but it is quite
smart.


Cheers,
Ryan



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

R Smith-2
In reply to this post by curmudgeon
[Repost: used a wrong e-mail at first, apologies if this comes through
twice]

On 2018/01/08 12:39 PM, x wrote:
> Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I imagined the RowID as being a separate index which is the root of my confusion. It would explain this

To elaborate a little - We often get people here asking "But why it
table-scans in stead of using my nice Index?".

This stems from an often-held misconception that Indexes are God-sent
magic to improve everything. The fact is that Indexes are costly
mechanisms which allows fast lookup which, only AFTER a certain critical
size and for specific circumstances, become more efficient than a scan.
The Query Planner has to do a lot of work to figure out what those
"critical size and specific circumstances" is for any specific query,
and it does get real fuzzy.

I think I've heard Richard or Dan explain it as follows (if memory
serves, someone please point out if I'm mistaken):

You can think of an SQLite table as essentially a btree covering Index
by itself with the Key being the Row_ID (or more recently, the PK for
WITHOUT ROWID tables). This is why the rowid (or any column serving as
an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL
values, but any other primary key could (in SQLite).

Being an Index by itself means that a Table-Scan is perhaps not as
inefficient as one might think and indeed using any other index means a
round-trip reading and hitting values in THAT index, then returning and
looking up the hit result in the rowid table index, and then reading the
pages(s) from it and extracting the data - where during a table scan,
all this round tripping is skipped.

So unless any prospective candidate Index for any query offers a truly
magnificent cost advantage, a table scan would probably be more
efficient, and so be chosen.  This is why running ANALYZE on large
tables is needed, because it allows the QP to better deduce whether a
prospective Index might in fact offer such a magnificent cost reduction
or not. Another way is hinting at the QP (Search "likelihood" in the docs).

This is why a non-rowid-alias Primary Key on a rowid table is also less
efficient to scan than the table itself (often very non-intuitive) - or
- why a covering index sometimes gets avoided in a JOIN when it seems to
contain all needed data to fulfill the join obligation.

Also, often a great index is not used simply because the query planner
does not know enough about it and its prospective cost to obtain a good
estimate of its utility, and sometimes what feels intuitively to us as a
great Index just isn't really. The QP is not infallible, but it is quite
smart.


Cheers,
Ryan



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

Dominique Devienne
On Mon, Jan 8, 2018 at 12:33 PM, R Smith <[hidden email]> wrote:

> using any other index means a round-trip reading and hitting values in
> THAT index,

then returning and looking up the hit result in the rowid table index,

and then reading the pages(s) from it and extracting the data -

where during a table scan, all this round tripping is skipped.
>

Plus during a table-scan, you're reading the table pages "in-order", and
decoding the page only once,
for all rows on each page. While an index-scan read the index "in-order",
but the table pages "out-of-order",
since most consecutive rows (from the index) end up on different table
pages. The page-cache helps to
avoid too much IO (if big enough), but you must still decode the page for
each row to access one specific record inside it.

Sometimes the full-table-scan is the most efficient indeed. --DD

PS: But also note that a query with an order-by or group-by might still
prefer an index-scan to avoid sorting rows,
  if the index already matches the requested order, offsetting the eventual
higher cost from the index-scan.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
In reply to this post by Dominique Devienne
Thanks Ryan and Dominique. The quote “You can think of an SQLite table as essentially a btree covering Index by itself with the Key being the Row_ID” makes things a lot clearer.



Thanks to everyone for their replies and patience.



Regards



Tom







________________________________
From: sqlite-users <[hidden email]> on behalf of Dominique Devienne <[hidden email]>
Sent: Monday, January 8, 2018 10:56:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On Mon, Jan 8, 2018 at 11:39 AM, x <[hidden email]> wrote:

> 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?
>

It depends if your ID column is an alias for the ROWID special column or
not.

ROWID is the key of the B-tree I believe, so if you insert a row "in the
middle",
many pages have potentially to be rewritten to "rebalance" the B-tree.
While only the "last" page needs updating with AUTO INCREMENT or a ROWID
larger than the previous larger one.

So if ID an alias for ROWID, the insert might be much more expensive.
If ID is not, then the row is simply "appended" at the end, "cheaply".

I could be wrong of course. I'll find out very soon :). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

Hick Gunter
In reply to this post by curmudgeon
>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

curmudgeon
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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
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:[hidden email]] Im Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

Hick Gunter
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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list <[hidden email]>
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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
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:[hidden email]] Im Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

David Raymond
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:[hidden email]] 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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list <[hidden email]>
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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
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:[hidden email]] Im Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

David Raymond
In reply to this post by Hick Gunter
Answering one of my own questions: You can't do a foreign key to a rowid unless it's an integer primary key.

http://www.sqlite.org/foreignkeys.html

"The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid."



-----Original Message-----
From: David Raymond
Sent: Monday, January 08, 2018 1:13 PM
To: 'SQLite mailing list'
Subject: 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:[hidden email]] 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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list <[hidden email]>
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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
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:[hidden email]] Im Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = NULL'

Hick Gunter
In reply to this post by David Raymond
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:[hidden email]] Im Auftrag von David Raymond
Gesendet: Montag, 08. Jänner 2018 19:13
An: SQLite mailing list <[hidden email]>
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:[hidden email]] 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:[hidden email]] Im Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list <[hidden email]>
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 <[hidden email]> on behalf of Hick Gunter <[hidden email]>
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:[hidden email]] Im Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <[hidden email]>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12