Do null values save storage space? [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

Space used by nulls in database

What's more efficient in terms of storage spac开发者_运维知识库e?

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

---

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

---

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.

---

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.

---

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.