How much size "Null" value takes in SQL Server

Question

I have a large table with say 10 columns. 4 of them remains null most of the times. I have a query that does null value takes any size or no size in bytes. I read few articles some of them are saying :

http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-4.html

There is a misconception that if we have the NULL values in a table it doesn't occupy storage space. The fact is, a NULL value occupies space – 2 bytes

SQL: Using NULL values vs. default values

A NULL value in databases is a system value that takes up one byte of storage and indicates that a value is not present as opposed to a space or zero or any other default value.

Can you please guide me regarding the size taken by null value.

Answer 1

If the field is fixed width storing NULL takes the same space as any other value - the width of the field.

If the field is variable width the NULL value takes up no space.

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.

---

The reason for the discrepancies that you have observed in information from other sources:

• The start of the first article is a bit misleading. The article is not talking about the cost of storing a NULL value, but the cost of having the ability to store a NULL (i.e the cost of making a column nullable). It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns).

• The second link seems to be a question about Microsoft Access. I don't know the details of how Access stores NULLs but I wouldn't be surprised if it is different to SQL Server.