Consider the following information coming from Microsoft SQL docs pages:
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
So, if I create a new table like this:
CREATE TABLE MyTable ( Id int PRIMARY KEY, Value1 bit, Value2 bit, Value3 bit, Value4 bit, Value5 bit, Value6 bit, Value7 bit, Value8 bit)
...then all the bit columns (Value1, Value2, ... Value8) in one row should take up one byte. Or?
When we take a closer look at it, Value1 field of a row can have values 0, 1 or NULL inside. This means that field is actually of a 3-state value, or a ternary value. 2 such values can represent dictionary of 3*3=9 values. 8 such values are a dictionary of 6561 values.
If we're thinking about the memory footprint of such a row, 8 bits should take up one byte. This means that this one byte encodes 6561 values instead of 256 values. This is obviously wrong.
So, either MS SQL docs are misleading or we're talking about some quantum computing here. Of course it's the former, but what I'd really want to know is the answer to this question: how are nullable bits (and of course that SQL Server bits are nullable by default) really stored in SQL Server underlying structures?