SQLite: Using ROWID in lieu of a timestamp

Question

Can I use the ROWID in place of a timestamp in an SQLite table?

I need to get the 50 most recent items of an SQLite table, I was thinking about using a separate timestamp field, but then I figured a bigger ROWID means a newer item, and the ROWID is already there, no need to change the schema.

Answer 1

Well, as it says here (with my emphasis):

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

However, it also says:

Rowid values may be modified using an UPDATE statement in the same way as any other column value can, either using one of the built-in aliases ("rowid", "oid" or "rowid") or by using an alias created by an integer primary key.

It would certainly be faster, but it sort of hurts my feeling for "open" design, in that you're relying on a feature of the implementation rather than making it specific.

Having said that, the same link also says this:

With one exception noted below, 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. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

Which I think gives you the perfect answer:

Define an INTEGER primary key on your table and use that for selection. You'll get the speed of using the ROWID (because as it says above, it's just an alias) and you'll get visibility in the schema of what you're doing.