On 10 Mar 2011, at 3:31pm, Dave White wrote: > 1. Do I understand this correctly?
Not for SQLite. It doesn't do keying arrays so don't worry about gaps in arrays or anything like that. > 2. If necessary, I can manually recycle rowids to fill in these gaps, > and maintain a separate UID that will never be re-used. For the sake of > selection performance, is this a better solution? No need. The rowid is just a 64-bit signed INTEGER value in a column. Just let SQLite assign its own values to the rowids using its own AUTOINCREMENT functionality. You can read a little about it here: <http://www.sqlite.org/autoinc.html> > 3. Assuming a record isn't removed, can I rely on a rowid to remain > stable? Or can it ever be changed by some event, for example a Vacuum? Do > rowid gaps ever get "compressed"? That's the right question, and yes, under some circumstances SQLite can change row ids: <http://www.sqlite.org/lang_vacuum.html> "The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY." So make the primary key explicit: for every table that you want stable rowids for, explicitly define an id column (call it what you want) as follows: CREATE TABLE suits ( id INTEGER PRIMARY KEY AUTOINCREMENT, [other columns] ); With a table like this, although SQLite will realise it can alias the 'id' column to its internal rowid, SQLite commands will know that you have defined that column yourself. Therefore you might actually be reading values from it, so it should not change those values. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
