CODEWITHSUNDEEP
sqlite
David
David

Reputation: 5016

Order of data in SQLite database

If I was to insert lots of rows into an empty table without primary key, nor any indexes. Varying number of rows might be inserted per transaction. Could I then be sure that a SELECT * FROM the_table; would retrieve the data in the same order on both Linux and Windows?

Upvotes: 1

Views: 1495

Answers (1)

Mat
Mat

Reputation: 206727

No, you cannot and should never rely on the order of rows in a result set from a query that does not have ordering constraints. Even on the same platform, same database. Even if it works in your tests.

Things like VACCUMing your database (or some of the auto_vaccum modes I think) could change the relative block layout of your data and alter the result set even if nothing else has changed elsewhere (no inserts, no query plan change).

Upvotes: 3

Related Questions