14

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)

Community
  • 1
  • 1
Alex J
  • 9,905
  • 6
  • 36
  • 46
  • I'm working with 2008 R2, but if other versions behave differently, it'd be nice to know. – Alex J Dec 28 '10 at 13:10
  • D'oh, I searched for "null storage", but not "null space". Agreed for duplicate, the answers there answer this question. – Alex J Dec 28 '10 at 13:20

4 Answers4

14

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

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Is there a reference for this or do you know it from experimental evidence? – Alex J Dec 28 '10 at 13:13
  • 8
    I know because this gets asked almost every week. ;-) – Mark Byers Dec 28 '10 at 13:22
  • I probably don't quite understand *when* the space for a column (or, actually a row) is reserved by SQL Server, but the fact is that adding a nullable column to a huge database is actually instantaneous and doesn't seem to involve any allocation (which happens if you specify NOT NULL with a default value, for example). Does this mean SQL Server will allocate a bunch of space for this new column when you first try to write a row containing that value? – vgru Sep 17 '15 at 07:57
5

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
4

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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
2

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.

Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
  • 1
    In this case the field is just an application flag, and checking for zero or for null is the same, hence just the storage consideration. – Alex J Dec 28 '10 at 13:18