SQL Server 2014 - varchar(x) NULL size

Question

The subject of NULL values size has been discussed in many topics like this one in SO.

I could understand that if the row is of fixed size, int or varchar(x) NULL valeus take space (zeros if the attribute is int or spaces if the attribute is varchar(x)) + 1 byte for NULL bitmap.

I created a table with two attributes:
CREATE TABLE Person (id int NULL, name VARCHAR(50) NULL).

And used the script here to get for each row the size in B:

I could read from the table these facts:
- The first three rows: 1 byte per each letter + 1 byte for NULL bitmap.
- The 4th row: 2 NULL bitmaps.
- The last two rows: 4 bytes of int + 1 byte for NULL bitmap.

Why the int/varchar(50) NULL values above didn't take all the declared size?

I'm using SQL Server Management Studio 2014. Did norms change?

Answer 1

1. The script you used is wrong. Using datalength() will not retrieve physical length.
2. Your expectations are incorrect. Variable length fields do not take 'spaces' for NULLs, and actually ints may also not take 0s, subject to row compression.

Use sys.dm_db_index_physical_stats to retrieve min_record_size_in_bytes, max_record_size_in_bytes and avg_record_size_in_bytes. Read SQL Server table columns under the hood and Inside the Storage Engine: Anatomy of a record for an explanation of record size. Read Row Compression Implementation to better understand some alternatives.