Read/Write order of tables with rowid

(1) By Aask (AAsk1902) on 2022-11-03 06:29:47 [link] [source]

If I insert records in a table with (implicit) rowid in a loop:

Are the records always inserted into the table in chronological order?
Are the records always read in the order inserted?

Or should I create a table without rowid and have a column emulating rowid and on reading the records sort in ascending order of that column?

(2) By Gunter Hick (gunter_hick) on 2022-11-03 07:25:58 in reply to 1 [link] [source]

This way lies madness.

SQL and relational algebra are about sets. Sets do not have an order. A collection of elements in a specific order is called a permutation. The way to transform a set into a permutation is to specify an ORDER BY clause.

The implicit rowid is an implementation detail of SQLite. While rowids are usually assigned in an ascending manner, there may be gaps (from rowids of rolled back inserts, or from deleted records, or ...). Some circumstances may require SQLite to pick a random rowid, which may be smaller than the last inserted rowid.

Implicit rowids without an explicit alias may not be preserved across VACUUM.

Never assume an ordering relation between records unless it is based on attributes you have specified.

Never assume records will be returned in a specific oder unless you have specified that order with an ORDER BY clause. Unordered SELECT statements will return their result set in visitation order. Visitation order depends on the query plan. Query plans may change between versions of SQLite and runs of ANALYZE as well as schema changes like adding or removing indices. Or even based on record counts.

(4.3) By Aask (AAsk1902) on 2022-11-03 09:22:57 edited from 4.2 in reply to 2 [link] [source]

In the forest of rhetoric about rowid, I discern that the answer to both my two specific questions lies in this statement:

Unordered SELECT statements will return their result set in visitation order. Visitation order depends on the query plan*.

which is volatile both within and across SQLITE versions.

In other words, the answer to both specific questions is simply in the negative.

Therefore:

I should create a strict table without rowid, with two columns (rownumber INT and value TEXT), and then insert the values/rows.
On completion, I should create an index on rownumber (not before so as not to slow down the insertions).
On reading, I should order by my rownumber column (the index should speed up the read).

Correct?

The reason for asking is this:

When I select * from myTable order by rownumber offset n limit m order by rownumber; does the offset apply before or after the order by?

(5) By Gunter Hick (gunter_hick) on 2022-11-03 09:42:12 in reply to 4.3 [link] [source]

Still No. You CANNOT create a WITHOUT ROWID table lacking a PRIMARY KEY.

Solution 1:

CREATE TABLE stuff (rowid INTEGER PRIMARY KEY, value TEXT);

INSERT a specific value for ROWID, never let SQLite pick one.

Solution 2:

CREATE TABLE stuff (rowid INTEGER PRIMARY KEY, value TEXT) WITHOUT ROWID;

then it is an error to insert a record without stating the rowid.

In any case, always give an ORDER BY clause *if the order matters*

Answering the additional question:

OFFSET and LIMIT are applied to the permutation (i.e. after ordering)

Hint: Do not use OFFSET and LIMIT to "page through" a table, this gets dismally slow very quickly, since you have to visit offset+limit rows. See discussion here https://sqlite.org/forum/forumpost/e4ed7710f7

(7) By Kees Nuyt (knu) on 2022-11-03 11:38:10 in reply to 4.3 [link] [source]

On completion, I should create an index on rownumber (not before so as not to slow down the insertions).

No, if you declare a column PRIMARY KEY or UNIQUE , any database engine takes care of indexing it. In the case of SQLite, UNIQUE will create an index called sqlite_autoindex_tablename_[1..N] , and INTEGER PRIMARY KEY will use the of the table B-Tree itself as the index.

-- 
Regards,
Kees Nuyt

(8) By Aask (AAsk1902) on 2022-11-03 11:59:25 in reply to 7 [link] [source]

Learnt something new; thank you.

(3) By Chris Locke (chrisjlocke1) on 2022-11-03 08:00:08 in reply to 1 [link] [source]

Gunter's answer is spot on, so I'll just add my little nugget.

row IDs are used internally by SQLite. Use them for information purposes only. If you need to order by, reference by or link by an ID, then use your own ID.

Example. Never assign something in the real world an ID given to you by a row ID. So don't put that invoice is invoice #567. Or that tube of toothpaste is now stock #987. If for some reason you have to rebuild your database or do something, there is no guarantee invoice #567 will be invoice #567.

(6) By Gunter Hick (gunter_hick) on 2022-11-03 09:52:33 in reply to 1 [link] [source]

Adding a technically correct but useless answer:

1) Yes. Anything else would be time travel.
2) No. Insertion order is not preserved.

If you want insertion order preserved, you must fill a field of your record with a montonically increasing value and ORDER BY this field when reading.

(9.20) By cj (sqlitening) on 2022-11-04 17:08:47 edited from 9.19 in reply to 1 [source]

create table t1(rowid integer primary key autoincrement,key text)
insert into t1 values(null,'B')
select * from t1 order by key,rowid

When reading by key is rowid needed in select statement since inserted with autoincrement to keep in chronological order?

If an index is created on key should it be (key,rowid) to stay in chronological order?

(10) By Chris Locke (chrisjlocke1) on 2022-11-04 17:17:13 in reply to 9.16 [link] [source]

You do not need to create a field called rowid. Rowid is already there.

You can prove this by creating a table with a field called 'field1'. You can then select rowid.

https://i.imgur.com/cDJIpvz.png

'Autoincrement' is only needed if you don't want to resuse rowIds. Normally, if you delete a bunch of records and delete some*, the rowIds get reused.

*The next rowId is the next highest number in the table. So if you create record 1,2 then 3, and delete record 2, it won't be reused yet - the next record will be 4. However, if you delete 4 then 3 (leaving only record 1) the next record will be 2. This sort of breaks if you manually edit the primary key, but hey.

When reading, you only select the fields you need. Selecting doesn't affect the output.

You don't store records in chronological order. Records are shoved in a bucket of records. You pull them out in the order you want them.

(11) By Scott Doctor (scottdoctor) on 2022-11-04 18:01:32 in reply to 9.20 [link] [source]

Please stop hitting submit so many times. Proofread your writing and think about it before hitting submit. This is causing WAY TOO MANY email notifications.

(12) By Warren Young (wyoung) on 2022-11-05 00:01:12 in reply to 11 [link] [source]

You can opt out of receiving edit notifications.

(13) By cj (sqlitening) on 2022-11-05 02:16:08 in reply to 11 [link] [source]

Sorry, didn't think of that.  Thanks!