Storage space used for NULL values

All we need is an easy explanation of the problem, so here it is.

According to below links, NULL values take some storage space:

https://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server
https://www.sqlservercentral.com/forums/topic/null-storage-2

If the column has fixed width data type – NULL occupies the length of the column

char(10) NULL takes 10 bytes
INT NULL takes 4 bytes

If the column has variable width – NULL takes 0 bytes

varchar(1000) NULL takes 0 bytes (+ 2 bytes of varchar column overhead ?)

Plus there is an overhead for having a nullable column. Information in links is a bit contradictory.
Can anyone shed some light on this ?

Here are my questions:

  1. Some people say that the overhead for having nullable column is 1 bit per row, some that it is 1 byte per row
    Which one is true ? I assume is 1 bit per row (so 8 rows make 1 byte), am I right ?

  2. For NULL values in varchar, there is still 2 byte overhead per row – is it actually true ?

  3. Most important question:
    I have a table with ~250 million rows of data, total size is ~115 GB
    I’ve added 7 columns using below code, checking the size of the table using sp_spaceused after each command

added columns:

alter table MyTable add TestVarchar10 varchar(10) NULL      
alter table MyTable add TestVarchar1000 varchar(1000) NULL  
alter table MyTable add TestVarchar10DefaultTest varchar(10) NULL default 'test'        
alter table MyTable add TestVarchar10DefaultTestWithValues varchar(10) NULL default 'test' with values  

alter table MyTable add TestINT int NULL
alter table MyTable add TestINTDefault99 int NULL default 99
alter table MyTable add TestINTDefault99WithValues int NULL default 99 with values

And any of additional columns, did not increase the size of the table at all
How can this be ? I would expect table size to change as NULL storage takes space

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Some people say that the overhead for having nullable column is 1 bit
per row, some that it is 1 byte per row Which one is true ? I assume
is 1 bit per row (so 8 rows make 1 byte), am I right ?

The null bitmap is stored at the row level so 1 byte is needed for each 8 columns of every row. The overhead is incurred for each row regardless of whether the column is defined as nullable or not (since SQL 2000).

For NULL values in varchar, there is still 2 byte overhead per row –
is it actually true ?

Column offset is stored for NULL variable-length columns except for the last one(s) so NULL values in variable length may incur a 2-byte overhead depending on the position.

Most important question: I have a table with ~250 million rows of
data, total size is ~115 GB I’ve added 7 columns using below code,
checking the size of the table using sp_spaceused after each command

This is where it gets interesting. SQL Server Enterprise Edition (or Developer Edition) includes optimizations such that some schema modifications are only meta-data operations. If you run the same test on Standard or Express edition, space will double.

See Paul Randal’s Anatomy of a record blog post. Although old, most still applies to current SQL Server version, including Azure SQL Database.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply