On disk a record basically looks like: Here are 5 values: value1, value2, value3, value4, value5
If your query is looking for the 6th, 7th or 8th field and the record on the disk only has 5, then it goes "<shrug> I guess they should be the default values for the missing fields." What that means is that when you add a new field it doesn't have to re-write the table because it handles the "missing on disk" fields just fine. There's actually a compile option for SQLite to intentionally do this all the time and leave out as many trailing NULL fields as it can to save space. I think it's SQLITE_ENABLE_NULL_TRIM, which is disabled by default. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of x Sent: Wednesday, October 23, 2019 8:53 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] ALTER TABLE ADD COLUMN From the documentation “A record might have fewer values than the number of columns in the corresponding table. This can happen, for example, after an ALTER TABLE ... ADD COLUMN SQL statement has increased the number of columns in the table schema without modifying preexisting rows in the table. Missing values at the end of the record are filled in using the default value for the corresponding columns defined in the table schema.” Suppose you have a table with say 5 columns that are almost always the default value (probably zero or null). Does the above suggest you should make them the last 5 columns in the table as the last n columns that are the default value won’t take up space? Or does this state just exist after ADD COLUMN but any rows added thereafter use the space? *Assume the 5 columns are little used so it doesn’t matter that they are the last named columns. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
