An empty column still occupies part of an ordinary stored row. NULL storage depends on the column type and row format. Measure the actual record before calling missing values free.
Read NULL Storage in the Ordinary Row Format
I inspect data types before accepting a space estimate based on null percentages. A nullable fixed-width field and a nullable variable-width field behave differently. Their empty values don't have one universal cost.
An ordinary uncompressed row includes a NULL bitmap. It records whether each relevant column value is null. The bitmap and its supporting metadata consume space too.
The bitmap identifies absence without turning every fixed-width field into variable storage. A nullable int still belongs to the fixed-width portion. A NULL char column still reserves its declared width in that ordinary layout.
These statements concern the uncompressed nonsparse row format. Compression and sparse storage change the representation. Keep those options out of the initial comparison so the result has a clear cause.
The bitmap's cost also changes at boundaries in the number of columns. Adding another nullable field doesn't always add only one bit to the total row allocation. Row metadata deserves attention alongside payload bytes.
Compare Fixed and Variable Sample Columns
Create the following tables in a disposable database. They share the same clustered identifier and sample population. Only the nullable description type differs.
The fixed sample uses char(300). The variable sample uses varchar(300). Every supplied description starts as NULL so the comparison emphasizes their missing-value behavior.
The generated inputs aren't measured production rows. Read the loaded counts if you need them. The declared width is a schema choice, not an observed disk-size claim.
Don't use DATALENGTH of NULL as the complete row-size measurement. It returns NULL for that expression. It doesn't report the bitmap, offsets, headers or reserved fixed portion.
Use the physical record statistics in the next step. Those inspect stored records rather than one expression's payload. The distinction prevents a misleading zero-byte conclusion.
CREATE TABLE dbo.NullFixedDemo
(ItemId int NOT NULL PRIMARY KEY, DescriptionText char(300) NULL);
CREATE TABLE dbo.NullVariableDemo
(ItemId int NOT NULL PRIMARY KEY, DescriptionText varchar(300) NULL);
INSERT dbo.NullFixedDemo(ItemId, DescriptionText)
SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY object_id), NULL FROM sys.all_objects;
INSERT dbo.NullVariableDemo SELECT ItemId, NULL FROM dbo.NullFixedDemo;
SELECT COUNT_BIG(*) AS FixedRows FROM dbo.NullFixedDemo;
SELECT COUNT_BIG(*) AS VariableRows FROM dbo.NullVariableDemo;Read the Stored Record Size
sys.dm_db_index_physical_stats with DETAILED exposes average, minimum and maximum record size. It also reports page and record counts. Filter to the leaf-level in-row records for this comparison.
The query applies the function to each selected table. index_id one selects the clustered index in this controlled setup. A different table design needs its actual index identifier.
DETAILED can read substantial data on a large table. Use a representative copy or an appropriate inspection window. The sample doesn't justify repeatedly scanning production allocation structures.
Record size isn't the same as total reserved space per row. Page headers, free space and allocation boundaries also affect total capacity. Keep record and allocation measurements separate.
NULL storage becomes concrete when both measurements are available. Read actual values from the query rather than guessing a reduction. The sample illustrates the mechanism, not a published savings figure.
SELECT t.name AS TableName, p.index_id, p.page_count, p.record_count,
p.avg_record_size_in_bytes, p.min_record_size_in_bytes, p.max_record_size_in_bytes
FROM sys.tables AS t
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, 1, NULL, 'DETAILED') AS p
WHERE t.name IN (N'NullFixedDemo', N'NullVariableDemo')
AND p.index_level = 0 AND p.alloc_unit_type_desc = N'IN_ROW_DATA';
SELECT OBJECT_NAME(object_id) AS TableName, SUM(used_page_count) AS UsedPages
FROM sys.dm_db_partition_stats
WHERE object_id IN (OBJECT_ID(N'dbo.NullFixedDemo'), OBJECT_ID(N'dbo.NullVariableDemo'))
GROUP BY object_id;
Account for Variable-Length NULL Storage
A NULL varchar contributes no ordinary string payload. Variable-length storage still uses metadata describing the variable portion and offsets. Trailing null columns can have additional layout optimizations.
That means its cost isn't automatically the declared maximum length. It also isn't automatically zero total row overhead. Measure the actual arrangement when several nullable columns are involved.
A populated variable value consumes its actual encoded bytes plus applicable metadata. Multibyte encodings and Unicode types change those bytes. A character count isn't always a byte count.
Column order and row-overflow behavior affect broader layouts. The simple two-column example deliberately avoids those extra structures. Add representative columns when extending the test to your table.
I keep these details separate from the SQL meaning of NULL. Storage changes don't make NULL equal to an empty string. The query still needs IS NULL for absence testing.
Test Sparse NULL Storage with the Same Population
Sparse columns optimize absent values by omitting their value storage. Populated sparse values carry extra overhead. The percentage of nulls and the data type determine whether the tradeoff saves space.
Sparse columns must be nullable. They have restrictions on defaults, types and other features. Review the documented restrictions before proposing the option for an existing schema.
The sparse sample below keeps the same identifiers and initially missing descriptions. Read its physical statistics beside the ordinary tables. Don't infer its total savings from a specification table alone.
Updates have sparse-specific overhead too. A very wide row that looks acceptable at rest can encounter limits during modification. Test the populated update path as part of the design.
A sparse flag doesn't transform every type into a universally smaller value. A dense population can cost more. The correct comparison uses the actual null distribution and update workload.
CREATE TABLE dbo.NullSparseDemo
(ItemId int NOT NULL PRIMARY KEY, DescriptionText char(300) SPARSE NULL);
INSERT dbo.NullSparseDemo SELECT ItemId, NULL FROM dbo.NullFixedDemo;
SELECT page_count, record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.NullSparseDemo'), 1, NULL, 'DETAILED')
WHERE index_level = 0 AND alloc_unit_type_desc = N'IN_ROW_DATA';Include Non-NULL Values in the Rehearsal
Populate the same selected rows in all three tables. That makes the sparse overhead visible alongside the null advantage. Repeat the physical-size inspection with the changed population.
The update below is a sample distribution choice. It isn't a reported production null percentage. Change it to match a representative copy when assessing a real design.
Compression supplies another alternative for ordinary tables. Row compression uses a different representation for fixed-width values and optimizes nulls. The earlier fixed-width rule no longer describes that compressed layout.
Don't combine sparse and compression choices without checking feature restrictions. Sparse designs and compressed tables have incompatible combinations. Treat them as separate candidates for the applicable schema.
What fraction of this column is populated during normal business use? Include that question before selecting sparse storage. A mostly empty test table can overstate its value.
UPDATE dbo.NullFixedDemo SET DescriptionText = 'Populated sample' WHERE ItemId % 10 = 0;
UPDATE dbo.NullVariableDemo SET DescriptionText = 'Populated sample' WHERE ItemId % 10 = 0;
UPDATE dbo.NullSparseDemo SET DescriptionText = 'Populated sample' WHERE ItemId % 10 = 0;
ALTER INDEX ALL ON dbo.NullFixedDemo REBUILD WITH (DATA_COMPRESSION = ROW);
SELECT data_compression_desc FROM sys.partitions
WHERE object_id = OBJECT_ID(N'dbo.NullFixedDemo') AND index_id = 1;Choose the Representation from the Whole Workload
I compare writes and reads after comparing size. A smaller stored record can still require extra processing. Include the indexes and common projections in that review.
NULL storage is a physical design question with several valid answers. Start with the actual type and row format. Then measure the layout and test its modification behavior.
Keep absence semantics separate from storage optimization. Replacing NULL with a made-up value changes the data contract. Empty compartments still belong to the tray you chose.
Related reading on this blog: Performance Benefit of Using SPARSE Columns? and Row and Page Compression: Estimating Savings Before You Compress.
A NULL is not one universal storage cost, it is an absent value represented by a particular row format.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
