CODEWITHSUNDEEP
sql-server
rileymat
rileymat

Reputation: 523

Storage of Bit columns for null values?

The Microsoft Documentation at https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-2017 says:

An integer data type that can take a value of 1, 0, or NULL.

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. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0. Converting to bit promotes any nonzero value to 1.

How is it possible to store 1, 0 and NULL in a single bit?

Upvotes: 5

Views: 10925

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

There is a NULL bitmap mask in the row header that keeps track of what columns is null or not.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Quoting a canonical answer by @MarkByers in the question How much size “Null” value takes in SQL Server regarding how SQL Server stores NULL in general:

In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.

So, I would expect the BIT type to behave the same as any other column, meaning that there would be a separate bit to keep track of whether the column be NULL or not NULL. Therefore, a BIT column in SQL Server actually uses two bits to keep track of the three values.

Upvotes: 4

Related Questions