SQL Server nullable data types size

Question

What is the size of nullable data types in Microsoft SQL Server DBMS?

For example, non-nullable int should take 4 bytes, how much space will be dedicated to nullable column?

Subquestions: nullable int, char(N), nvarchar(N) - I assume they might be stored differently.

What I've read:

• Where to find the size of SQL Server data types - good way to get sql types list and their size for my version of SQL server. But doesn't say a word about nullable types.
• http://msdn.microsoft.com/en-us/library/ms189124.aspx - there is a formula for calculating variable-size columns space required: "Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size". It's very strange: why it contains *2 multiplier (nothing told about nvarchar - this formula is for all variable sized types as comes from explanation); it must be a typo that Max_Var_Size is added rather than multiplied; and finally it contains +2 bytes for storing the length of the value, but again contains nothing for storing NULL values. As I understand it's possible to use 3 remaining bits of the value-length 2 bytes to store NULL identifier, but is it stored this way really?
• How much size "Null" value takes in SQL Server - as for me top answers are confusing. @Mark Byers said "If the field is fixed width storing NULL takes the same space as any other value - the width of the field", but it is not possible to store the standard integer value interval and additional NULL value in the same count of bits. Then "If the field is variable width the NULL value takes up no space" - again storing a NULL can't take no space at all - it has to store some marker for null value. Similar confusion with other answers there: somebody say it takes 2 additional bytes, somebody - that only 1 byte.
• http://home.clara.net/drdsl/MSSQL/DataTypes.html - nice table with types sizes, but again nothing dedicated to NULL values.

Answer 1

Nullable columns and non-nullable columns occupy exactly the same storage on a data page. Part of each data page is the null-bit-map, which has a bit for every column in the table, even non-nullable ones.

It is a common misconception that the null-bit-map section of the data page only stores bits for nullable columns. This is not true. The null-bit-map section contains nullable flags for all columns in the table. Here is a good reference explaining this myth. Here is another.

I have wondered why SQL Server (and previously Sybase) use this structure. One possibility is that changing the nullability of a column can be a "fastish" operation. Although the bit much change on all the pages, there is no danger of page splits by introducing a new NULLable field.

Another possibility is that it decouples, a bit, the layout on the page from the table metadata. Although the page does not know column names, it does know everything about columns based on column indexes.