Graham, as you probably realise from reading David & Simon’s replies, it’s really about avoiding those type code bytes altogether. You can use ADD COLUMN to add 10 columns to a billion row table in microseconds so obviously all that’s changed is the header. SQLITE_ENABLE_NULL_TRIM answers my question although Simon’s point about SQLITE_ENABLE_DEFAULT_TRIM is valid. I see that enabling it might trigger some obscure bug w.r.t. blobs and that it may be enabled by default in future versions.
________________________________ From: sqlite-users <[email protected]> on behalf of Graham Holden <[email protected]> Sent: Wednesday, October 23, 2019 2:09:19 PM To: SQLite mailing list <[email protected]> Subject: Re: [sqlite] ALTER TABLE ADD COLUMN Wednesday, October 23, 2019, 1:53:10 PM, x <[email protected]> wrote: > 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? I believe it can only happen after an ADD COLUMN, however, zero or NULL values will, essentially, take zero space whereever they are in a row. If you look in-and-around: https://www.sqlite.org/fileformat.html#record_format, you will see that the "type code" used for each column in a row has specific values for "NULL" and zero (0 and 8, respectively). This means that where those NULL/zero occurs, no extra space is used to hold the value. Graham _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
