Storage space used for NULL values

Question

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

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

Answer 2

The correct answer for SQL Server is, "It Depends".

Without consideration for Row or Page compression, fixed width columns take the same number of bytes whether they're NULL, "EmptyString", partially full, or whatever.

For VARCHAR, @Dan Guzman has the right idea but it needs to be elaborated upon.

To summarize (and simplify using only VARCHAR as an example)... NULL VARCHAR columns take ZERO bytes ONLY if there are NO VARCHAR columns to the RIGHT that are populated (not NULL, not "Empty String"). Any and all NULL VARCHAR columns to the LEFT of a populated column will take 2 bytes each for the "Column Offset", which marks the "end byte" for each variable width value in the row.

That means that if you have 10 VARCHARS in a table that are all NULL, they all collectively take ZERO bytes. Add just 1 character to the right-most column and your row expands by a whopping 23 bytes.

 2 for the Variable Width Column Count 
20 for the 10 two-byte "Column  Offsets"
 1 for the actual byte added to the right-most VARCHAR
--
23 total bytes added to the row as a result of adding just 1 byte to the right-most VARCHAR.

Here's the link to an answer here on StackOverflow that shows what happens at the row level using an easy to read, formatted output from DBCC PAGE.

https://stackoverflow.com/a/73439645/313265

NVARCHAR works in an identical manner for NULLs and "Empty Strings". I have NOT tested with other variable width datatypes but my "unqualified opinion" would be that they would work the same way.