On 2/1/2014 12:26 PM, Klaas V wrote:
|Igor Tandetnik wrote: | On 1/30/2014 7:20 PM, James K. Lowden wrote: |or define a constraint | | colname boolean check (colname in ('Y', 'N')) | |Of course you can use 0 and 1, but there's no technical advantage|There is a tiny advantage. Values 0 and 1 are special-cased in SQLite's file format, and occupy only one byte each. 'Y' and 'N' require two bytes to represent. Since SQLite uses at least 8 bits there is enough space for three values: False, True, Undefined. One has to define standard values. For example -1 for false, 0 for undefined, all others for true. YMMV. Using a letter has the advantage of being more explicit.
See http://www.sqlite.org/fileformat.html#record_format . For each cell, there's at least one byte indicating the data type (possibly more for strings and blobs, since the length is also encoded into the type), followed by the actual data. Values NULL, integer 0 and integer 1 are special - they are encoded into the type, which is not followed by any data bytes. Values 'Y' and 'N' are not special - each will consume one byte for the type, and one byte to store the actual character.
The special case for 0 and 1 exists precisely for the purpose of efficiently representing boolean values.
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
