Multiple BIT datatype columns in the same table

Question

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.

See: https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017

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?

Answer 1

For a fixed length field (such as a bit value), the field will always take up the same amount of space - in your case one bit.

The NULL bitmap is in the data row itself - this stored whether a particular field is considered NULL or not. As such the field itself only has to store two values - 1 or 0 - the NULL bitmap handles whether the field is actually set to NULL or not.

The NULL bitmap has a bit for each column in the table regardless of whether the column is nullable, and this contains the "third value" you mention. Again the space for the null bitmap is rounded up to an integer number of bytes.

The following code demonstrates how SQL Server stores the NULL bitmap - you'll need to replace the page numbers if you run this yourself.

CREATE TABLE TestNullBitmap (
    TestText CHAR(10) NULL
    )
GO

INSERT INTO [dbo].[TestNullBitmap] ([TestText])
VALUES
('hello'),
('hello2'),
(NULL);

--Grab the page number
DBCC IND ([TestDb],TestNullBitmap,-1);
GO

--Use the page number from DBCC IND
DBCC PAGE ([TestDb],1,631032,3) WITH TABLERESULTS;
GO

For me I get the following results for the three data rows:

0000000000000000: 10000e00 68656c6c 6f202020 20200100 00 ....hello ...

0000000000000000: 10000e00 68656c6c 6f322020 20200100 00 ....hello2 ...

0000000000000000: 10000e00 68656c6c 6f322020 20200100 01 ....hello2 ...

The NULL bitmap is the change to that very last byte value - read in Little Endian the binary representation is 0000 0001 (IE The first column is NULL)

Rather interestingly you can also see that the system has stored the value from the previous INSERT as the actual data for the fixed length field - rather than 0'ing it out.