sql-server - tuning - which one of the following attributes must be used to optimize storage for null values

Do null values save storage space? (3)

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)

0 is not null. If you don't have a value for the field, don't stick a zero in there! I can't tell you how many times I've come across bugs that are essentially unfixable because we have no way of knowing whether the zeros in a production database were real zeros or fake nulls. Embrace nulls - yes, they require a bit more coding, but embrace them anyway.

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).

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.