Storage space used for NULL values

[rielyn]

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

[derk]

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 https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/ . Although old, most still applies to current SQL Server version, including Azure SQL Database.