On 2016/05/19 2:50 PM, Richard Hipp wrote: > On 5/19/16, Ertan K???ko?lu <ertan.kucukoglu at 1nar.com.tr> wrote: >> Hello, >> >> I wonder if there are any drawbacks/things to be aware of, etc. for tables >> created "WITHOUT ROWID". I am considering using such tables in another >> project. > Experiment. Run your application with WITHOUT ROWID and measure > performance (where "performance" is defined by whatever is important > to you) then remove the WITHOUT ROWID and run the same experiment. > Decide which works best for you. > > All of the application code should work the same either way, so other > than removing the "WITHOUT ROWID" text from the "CREATE TABLE" no > other changes are required. >
True, though be aware that some SQLite functionality requires tables to have rowids, such as AUTOINCREMENT. Also, SQlite engines prior to 3.8.2 will not work and will report a malformed database. Any table using WITHOUT ROWID must have a primary key (this is otherwise not a requirement). Primary keys cannot have NULL values in WITHOUT ROWID tables - this is perfectly possible otherwise, though most of us feel it should never be possible anyway. If you ever use the last_insert_id type of API's in SQLite, it won't work on a WITHOUT ROWID table. (But you shouldn't anyway...) On-Update callbacks from the API doesn't work right (or at all - not tested recently) in a WITHOUT ROWID table The new SESSION extension requires ROWID tables ^[needs citation]. (I think - not tested yet) I think the SQLDIFF might also, but now I'm just guessing. My point is, check out all the kinds of things you might do in SQLite, make sure they can be done with WITHOUT ROWID tables. If they can, the change is worth it - the WITHOUT ROWID optimization is quite faster on large key lookups and nice and lean in size (no need for rowid translation or added rowid key data per table). Everywhere I use it, it works a charm, and if ever I find I want to use some functionality on a table that requires a rowid, the changeover is quick and painless, any DB admin system out there would probably have a 1-click solution to do so. Best of luck, Ryan
