Menu
Say I have a db column that's only used rarely by records in my sqlite db (for the rest of the records, the value is null), would those null columns consume as much space comparable to if those columns were non-existent?
367
One common misconception about NULL values is that they represent “nothing” or “no value.” NULLs are indeed a value. They take up space on your database hard drive as opposed to “nothing” that indicates there is no value.
A NULL value in databases is a system value that takes up one byte of storage and indicates that a value is not present as opposed to a space or zero or any other default value.
The maximum size of a database file is 4294967294 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or 281474 gigabytes or 256,000 gibibytes).
Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0. For variable size datatypes the acctual size is 0 bytes.
In my test program, NULL values have consumed one byte per row. If the average row size in your table will be above 100 bytes, then yes, it's comparable to nonexistent.
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
