Hello, I would like to store rows with an id column that is unique (at least most of the time, see below), but must be below some maximum value, and I would like to do so in a very space efficient way.
My idea is to use the rowid as the id column and declare it as autoincrement. After every insertion, I would check that the generated rowid is less than the maximum value. If the rowid exceeds the max value, I would reset the seq no in the sqlite_sequence table back to 1, and do some external processing to handle the fact that previously deleted rowids may now be reused. This seems to work in practice. After 'seq' is reset, new rowids start again with the smallest free value and skip occupied ones. However, can someone tell me if: a) this is reasonably efficient, e.g. if there are 10000 used rowids before the next free one, will sqlite try each one in turn until it finds an unused one, or can it jump directly to the unused one. b) how (un-)likely it is that future version of SQLite will behave in the same way. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
