4

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?

Community
  • 1
  • 1
rileymat
  • 503
  • 4
  • 13

2 Answers2

3

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • The quote isn't quite right though. *All* columns have a corresponding bit in the null bitmap, not just nullable ones. That's why a `not null` -> `null` change to a column is a metadata only update. – Damien_The_Unbeliever Nov 28 '18 at 06:42
  • @Damien_The_Unbeliever Then maybe edit that other question. Or, better yet, edit my question, and then show me some green! – Tim Biegeleisen Nov 28 '18 at 06:43
2

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

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281