SQLite SELECT default order with PRIMARY KEY ASC

Question

I've created a SQLite table using:

CREATE TABLE T1 (
  CN INTEGER PRIMARY KEY ASC,
  Name TEXT
);

If I do:

SELECT * FROM T1

Will I get the rows order by CN even without specifying a ORDER BY clause? Is CN an alias to ROWID?

Answer 1

There is no such thing as a default order, if you need your results ordered add an explicit order by clause.

The dbms is simply optimised to look for the best way to quickly get the required data based on the query. In this case it's the primary key on CN, but that's only because your example is so simple. Never ever rely on the dbms choosing the order you want.

Answer 2

The second question might be useful to others.

From the SQLite documentation:

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid".
... if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.

This also holds for columns that are declared of type "INTEGER PRIMARY KEY ASC", so in your table CN is an alias for "rowid"

Further information can be found here: http://www.sqlite.org/lang_createtable.html#rowid