On 9 Jun 2018, at 3:52pm, Ben Asher <[email protected]> wrote: > we would like to make sure that the space occupied by the columns contents is > zeroed and returned to SQLite's free list > (https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the column's > contents to "" (for a TEXT column > specifically) sufficient to do that?
I recommend you use NULL instead: UPDATE myTable SET deadColumn = NULL WHERE [whatever]; VACUUM; I have three reasons for preferring NULL: 1) NULL takes the minimum amount of space to store 2) handing NULLs is much faster than handling strings 3) NULL in SQL literally means "value missing" or "value unknown". [later seeing a followup] If you can't use NULL, use the integer 0. Even if the column has TEXT affiliation. The values NULL, 0 and 1 each take no storage (they are special value types) and all three values are handled very efficiently. VACUUM will do the actual returning of space and shrink the file size. Doing it with VACUUM does it all at once at a time of your controlling rather than slowing down subsequent operations. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
