On Sep 3, 2009, at 7:25 PM, Mark Spiegel wrote: > Jay A. Kreibich wrote: >> Integer values between -128 and 127 use only a single byte of >> storage above and beyond the header size that all values have. >> > Not quite. Values between 0 & 127 use 1 byte of storage. Negative > values use the full 9 bytes in my experience. (I'm setting aside the > integer 0 and integer 1 optimizations outlined in > http://www.sqlite.org/fileformat.html#record_format with that > statement.)
You are both right and both wrong. There are two different integer representations used in SQLite. (1) "varint" or variable length integer is an encoding of 64-bit signed integers into between 1 and 9 bytes. Negative values use the full 9 bytes as do large positive values. But small non-negative integers use just one or two bytes. Varints are used in places where integers are expected to be small and non-negative, such as record sizes in the btree (usually less than 100 bytes) and also for rowids. (2) When you store an integer into a column (a column other than the rowid) it is stored as a 0-, 1-, 2-, 3-, 4-, 6-, or 8-byte signed integer. The smallest possible representation is used, depending on the magnitude of the integer. The size used is actually recorded in a separate varint (the "type" varint) that also determines that the value stored is an integer and not (say) a string or blob or floating point number or NULL. A type varint of 1 means store a 1-byte integer. A type varint of 2 means store a 2-byte integer. And so forth. A type varint of 8 (I think) means the value is exactly 0 so don't store anything. 9 means the value is exactly 1. And so forth. Notice that the type varints are all small integers and are thus themselves represented by a single byte. Every value stored has a type varint. Additional data is stored as necessary. A zero-byte string or blob stores uses no space beyond its type varint. A NULL uses no space beyond its type varint. A numeric 0 or 1 uses no space beyond its type varint. An integer between -127 and +127 uses 1 additional byte beyond its varint. A 1MB blob uses a million bytes of additional space beyond its type varint. And so forth. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
