SQLite Insertion Order vs Query Order?

Will the order of rows returned by a query will be the same as the order in which the rows were inserted into the table, of SQLite database?

If Yes, Is this behaviour consistent?

If No, Can this be enforced?

I have a requirement of storing approx 500 rows of data, and which requires sorting/ordering from time to time. The data is in proper or开发者_StackOverflow中文版der, before the insertion.

---

Given the small number of rows in your table, this is probably what you need:

SELECT * FROM yourtable ORDER BY ROWID

For more information on ROWID, see these two links: SQLite Autoincrement and ROWIDs and the INTEGER PRIMARY KEY

---

Even if the order may be consistent in one scenario, there is afaik no guarantee.

That is why SQL has the ORDER BY operator:

SELECT foo,bar FROM Table FOO WHERE frobnitz LIKE 'foo%' ORDER BY baz ASC;

---

Will the order of rows returned by a query will be the same as the order in which the rows were inserted into the table, of SQLite database?

No, you can't count on that. All query optimizers have a lot of freedom when it comes to speeding up queries. One thing they're free to do is to return rows in whatever order is the fastest. That's true even if a particular dbms supports clustered indexes. (A clustered index imposes a physical ordering on the rows.)

There's only one way to guarantee the order of returned rows in a SQL database: use an ORDER BY clause.