Space Used by Sparse Data: How SPARSE Columns Save Bytes

Space used by sparse data depends on how many values are NULL. A SPARSE column takes no space for a NULL, but it takes more space than normal for a value. This post measures a normal table and a SPARSE table with 100,000 rows. Then it finds the point where SPARSE stops paying off.

The Idea

A fixed-length column such as INT or MONEY always takes its full size, even for a NULL. A MONEY column costs 8 bytes in every row, whether it holds a value or not. When 99 rows in 100 are NULL, those bytes add up.

Think of an optional field, such as a promo code on an order. Nearly every row leaves it empty. SPARSE, added in SQL Server 2008, tells SQL Server to store nothing for a NULL. Smaller rows mean fewer 8 KB pages to store, read and cache.

Two Coupon Tables

Both tables below hold coupons with a Discount column of type MONEY. In the second table, Discount is SPARSE. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE CouponsPlain (CouponID INT NOT NULL, Region CHAR(2) NOT NULL, Discount MONEY NULL);
CREATE TABLE CouponsSparse (CouponID INT NOT NULL, Region CHAR(2) NOT NULL, Discount MONEY SPARSE NULL);
GO

A sparse column must allow NULL. This definition fails with message 1731, which says a sparse column must be nullable.

CREATE TABLE CouponsBad (CouponID INT NOT NULL, Discount MONEY SPARSE NOT NULL);

Load 100,000 Coupons

Only 1 coupon in 100 gets a discount. The rest are NULL. The variable @OneIn controls the mix, and you can change it later.

TRUNCATE TABLE CouponsPlain;
TRUNCATE TABLE CouponsSparse;
DECLARE @OneIn INT = 100;
INSERT INTO CouponsPlain (CouponID, Region, Discount)
SELECT value, 'NW', CASE WHEN value % @OneIn = 0 THEN 9.99 END FROM GENERATE_SERIES(1, 100000);
INSERT INTO CouponsSparse (CouponID, Region, Discount)
SELECT value, 'NW', CASE WHEN value % @OneIn = 0 THEN 9.99 END FROM GENERATE_SERIES(1, 100000);

Measure the Space

The first query reads the average record size and the page count from a dynamic management function. DETAILED mode reads every page, so it takes a moment on a large table. The second query adds up the pages used.

SELECT 'CouponsPlain' AS TableName, record_count AS Coupons, avg_record_size_in_bytes AS AverageRowBytes, page_count AS Pages
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('CouponsPlain'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
UNION ALL
SELECT 'CouponsSparse', record_count, avg_record_size_in_bytes, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('CouponsSparse'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

A plain row is 21 bytes. That’s a 4-byte header, 6 bytes of fixed data, a 3-byte null block and 8 bytes for the MONEY column. The sparse rows average 13.2 bytes. The plain table uses 285 pages, and the sparse table uses 188.

SELECT OBJECT_NAME(object_id) AS TableName, used_page_count * 8 AS UsedKilobytes
FROM sys.dm_db_partition_stats
WHERE object_id IN (OBJECT_ID('CouponsPlain'), OBJECT_ID('CouponsSparse')) AND index_id = 0
ORDER BY TableName;

That’s 2,288 KB against 1,512 KB. Sparse saves 776 KB, about a third.

Where Sparse Stops Paying Off

Change 100 in @OneIn to 2, 3, 10 and other numbers, and run the load and the measure again. With 1 coupon in 2 holding a discount, the sparse rows average 23 bytes against 21, and sparse loses. With 1 in 3, they average 19.7 bytes, and sparse wins.

The reason is the price of a value. A sparse row with a discount is 33 bytes. The value costs 20 bytes, against 8 in a plain column. A sparse row with a NULL is 13 bytes, so the Discount column costs nothing. In this test with one sparse column, the break-even point is near 60 percent NULL.

When to Use SPARSE

Use SPARSE for a column that is NULL in most rows, such as an optional field on a wide table. Leave it off a column that holds a value in half the rows or more. Your break-even point depends on the data type and the number of sparse columns. Measure before you commit.

Clean Up

Remove the sample tables when you finish.

DROP TABLE CouponsSparse;
DROP TABLE CouponsPlain;

What to Remember

A NULL costs nothing in a SPARSE column, and a value costs more than in a normal column. SPARSE saves space only when most of the values are NULL.

To go further, read SQL SERVER – 2008 – Introduction to SPARSE Columns, SQL SERVER – Performance Benefit of Using SPARSE Columns? and SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server.

A sparse column is not a free saving, it is a bet that most values are NULL.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.