4

If you have a table with 20 rows that contains 12 null columns and 8 columns with values, what is the implications for storage and memory usage?

Is null unique or is it stored in memory at the same location each time and just referenced? Do a ton of nulls take up a ton of space? Does a table full of nulls take up the same amount of space as a table the same size full of int values?

This is for Sql server.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
joncodo
  • 2,298
  • 6
  • 38
  • 74
  • I'm not sure how nothingness can take as much space as a value. – BoltClock Sep 14 '11 at 18:54
  • 1
    The answer is completely implementation dependent. It's probably data type dependent within a database. What database are you interested in? – Larry Lustig Sep 14 '11 at 18:55
  • 1
    @BoltClock - In a fixed length non sparse column in SQL Server (without compression enabled) it does. – Martin Smith Sep 14 '11 at 18:56
  • 1
    This is a rumor that a college is telling me. I am unsure that he is correct. He is saying that if I have nulls in a database that it takes up space that we can't afford to pay for. How about next time before you downvote and close my question you just ask me what parts you are unsuure about. I would be happy to clarify as much as possible. – joncodo Sep 14 '11 at 18:58
  • 1
    @Jonathan - If you fix your question to be a lot more precise in what you are asking and specify RDBMS I'd probably vote to reopen. – Martin Smith Sep 14 '11 at 19:00
  • 1
    Nulls cost space. In the simplest case a null indicator is stored with each field inside the row. Sometimes the combined null indicators are stored as an array inside the row, most likely in front of it. But basically: because they are persistent they must be stored, and do cost space. – wildplasser Sep 14 '11 at 19:01
  • @wildplasser - This is not true for sparse columns in SQL Server but the tradeoff is that `NOT NULL` sparse columns take more space. – Martin Smith Sep 14 '11 at 19:15
  • I see. Well, storing only the not-null atrributes, together with their (row+)column-ids has always been a trick. This is how sparse matrices work, too. But it will cause extra complexity wrt free space management and locking. Also, management of intermediate tables (subqueries) can become cumbersome. – wildplasser Sep 14 '11 at 19:52

2 Answers2

9

This depends on database engine as well as column type.

At least SQLite stores each null column as a "null type" which takes up NO additional space (each record is serialized to a single blob for storage so there is no space reserved for a non-null value in this case). With optimizations like this a NULL value has very little overhead to store. (SQLite also has optimizations for the values 0 and 1 -- the designers of databases aren't playing about!) See 2.1 Record Format for the details.

Now, things can get much more complex, especially with updating and potential index fragmentation. For instance, in SQL Server space may be reserved for the column data, depending upon the type. For instance, a int null will still reserve space for the integer (as well as have an "is null" flag somewhere), however varchar(100) null doesn't seem to reserve the space (this last bit is from memory, so be warned!).

Happy coding.

4

Starting with SQL Server 2008, you can define a column as SPARSE when you have a "ton of nulls". This will save some space but it requires a portion of the values of a column to be null . Exactly how much depends on the type.

See the Estimated Space Savings by Data Type tables in the article Using Sparse Columns which will tell you what percentage of the values need to be null for net saving of 40%

For example according to the tables 98% of values in a bit field must be null in order to get a savings of 40% while only 43% of a uniqueidentifier column will net you the same percentage.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155