Do null values save storage space? [duplicate]

Question

Possible Duplicate:
Space used by nulls in database

What's more efficient in terms of storage space?

• A nullable int column full of nulls.
• An int column full of zeroes.

I know a nullable column takes up 1 extra bit of information to store its null state, but do null values save the database from allocating 32 bits for an int that is null?

(null semantics and the meaning of null are not important for this question)

Answer 1

For fixed width fields like nullable int the storage space required is always the same regardless of whether the value is null or not.

For variable width nullable fields the value ’NULL‘ takes zero bytes of storage space (ignoring the bit to store whether the value is null or not).

Answer 2

If you are using SQL Server 2008, and expect to have a significant number of NULL values in columns, I would suggest you investigate Sparse Columns. They are optimized for storing NULL values.

Answer 3

SQL Server 2008 introduced Sparse columns for columns that are primarily NULL.

A SPARSE column for a row, which is NULL uses 0 bits of storage. But you pay a 4 byte penalty if there is a value.