Sparse columns: cheap NULLs, taxed non-NULLs, and where the bet actually pays

SPARSE is one of those SQL Server keywords that sounds like free storage: tag a mostly-empty column SPARSE and its NULLs stop costing anything. That half is true. The other half — the half that decides whether you should actually do it — is that every non-NULL value in a sparse column costs more than it would in a regular column. So sparse is a bet: cheap NULLs paid for by pricier non-NULLs, and it only comes out ahead when the column is mostly NULL.

“Mostly” has a number, and it’s different for every data type. So I measured it: regular NULLable vs SPARSE, on SQL Server 2025, across seven data types and six NULL fractions, with the same timing methodology as the rest of the series. Each table is an id plus eight attribute columns of one type — a realistic “wide block of optional attributes” — built twice, identical except the SPARSE keyword.

First, the mechanism. A regular fixed-length column costs its full width on every row, NULL or not (a NULL bigint still reserves its 8 bytes in the row). A sparse column is the opposite: a NULL drops out of the row entirely (zero bytes), but a non-NULL value carries ~4 bytes of overhead — a 2-byte column id and a 2-byte offset inside a per-row “sparse vector.” Backing that overhead out of my numbers lands exactly there: a bigint table going from 0% to 50% NULL drops from 119.2 to 70.5 bytes/row, i.e. 48.7 bytes for 4 fewer non-NULLs = 12.16 each = the 8-byte value plus 4.16 of overhead.

There’s a break-even NULL%, and it’s set by the data width

Sparse-column storage as a percentage of regular, per type, across NULL fraction (under 100% = sparse is smaller; the crossing is the break-even):

Type (8 cols)	0%	50%	75%	90%	95%	99%	break-even*
`bit`	409%	273%	196%	145%	122%	94%	~98%
`int`	185%	115%	77%	52%	42%	31%	~60%
`decimal(9,2)`	171%	105%	69%	46%	37%	26%	~54%
`datetime2(3)`	155%	93%	59%	38%	29%	21%	~44%
`bigint`	149%	88%	55%	35%	27%	19%	~40%
`guid`	130%	72%	42%	24%	17%	11%	~26%
`varchar(50)`	126%	116%	108%	101%	97%	94%	~91%

*interpolated NULL% where sparse = regular bytes/row.

Among the fixed-length types the order is pure data width: the 4-byte overhead is a big tax on a 4-byte int (needs ~60% NULL to pay off) and a small one on a 16-byte guid (pays off by ~26% NULL). bit is the outlier at the top — a regular bit is bit-packed at one-eighth of a byte, so the 4-byte sparse overhead is enormous relative to it, and you need ~98% NULL before sparse wins.

Microsoft’s documented thresholds are a single-column guide — a wide block beats them

Microsoft publishes a break-even NULL% per type (bit 98%, int 64%, bigint 52%, datetime2 57%, uniqueidentifier 43%). My measured crossings come in lower — and the gap widens with width:

Type	Documented	Measured (8-col block)
`bit`	98%	~98%
`int`	64%	~60%
`datetime2`	57%	~44%
`bigint`	52%	~40%
`guid`	43%	~26%

That isn’t the docs being wrong — it’s the difference between one sparse column and a block of them. The per-row sparse-vector has a small fixed cost; the documented figure charges it to a single column, but with eight sparse columns that fixed cost amortizes across all eight, so sparse wins earlier. The wider the type, the more pronounced. Practical reading: the documented threshold is a safe upper bound; a table built around many sparse attributes breaks even sooner than the per-column number suggests. bit is the anchor that matches the docs exactly, because there’s nothing to amortize away its huge relative overhead.

The payoff at high NULL%, and the tax at low

Where sparse is supposed to shine — a column that’s almost all NULL — it shines hard. At 99% NULL every type collapses to ~14–15 bytes/row (basically just the id and an empty sparse vector), regardless of how wide the type is:

guid at 95% NULL: 7.2 MB vs 42.3 MB regular — 17% of the size.
bigint at 99% NULL: 19% of regular. datetime2 21%, int 31%.

And the tax is just as real in the other direction. At 0% NULL — a column that’s never empty — sparse is bigger across the board: int +85%, guid +30%, and bit a brutal 4.1×. Reach for sparse on a column that turns out to be densely populated and you’ve inflated the table for nothing. Sparse is not a default; it’s a bet on sparsity, and the bet can lose.

Variable-length types barely benefit

varchar(50) is the odd one out, and it’s instructive. Its regular bytes/row already falls as NULLs increase (80.6 → 15.7 from 0% to 99% NULL), because a variable-length NULL already costs almost nothing — no padding, just an absent offset. Sparse has very little left to reclaim: it doesn’t pull ahead until ~91% NULL, and even then by a hair. Sparse is a fixed-length-NULL optimization. Variable-length columns already handle NULLs efficiently; don’t bother making them sparse.

Reads and writes: a cold-IO win, a warm-scan tax

Storage is only half the question. I ran insert / warm scan / cold scan / seek / range / count_attr (a COUNT() over all eight attributes — the most sparse-decode-sensitive read) over a perf subset. Sparse as a percentage of regular (under 100% = sparse faster):

Op	guid @95	bigint @95	int @95	varchar @95	int @50
insert	23%	43%	97%	79%	~par†
cold scan (IO)	22%	55%	91%	88%	116%
warm scan	169%	168%	176%	171%	185%
count_attr	150%	154%	159%	162%	167%
seek	~par	~par	~par	~par	~par

†high single-shot variance; ms_min favours sparse, ms_avg favours the (bigger) regular table.

The read story is the same CPU-vs-IO tradeoff that shows up with compression, and for the same reason:

Cold (IO-bound) reads track the size ratio. Where sparse made the table much smaller — guid at 95% NULL is 17% of regular — the cold scan runs in 22% of the time (4.6× faster). It’s mostly an IO win: fewer pages to pull off disk. But at int 50% NULL, where the sparse table is bigger, the cold scan is slower (116%). The win is exactly as big as the shrink, no more.
Warm (cached) reads are slower, always. Once the pages are in RAM there’s no IO to save, so you only pay the sparse-vector decode CPU: warm scans ran 166–185% of regular. count_attr is the purest view of that cost — sparse held a near-constant ~141 ms regardless of type, because the work is decoding the sparse vector, not reading the values.
Inserts at high NULL% are faster — fewer pages and less log to write (guid 23%, bigint 43%). At low NULL% that reverses with the bigger table.
Seeks are a wash either way.

So the read/write verdict mirrors storage: when sparse actually shrinks the table, you get a real cold-IO and insert win; when it doesn’t, you get a warm-CPU penalty for nothing.

The restrictions you’ll hit (with the real error numbers)

Sparse isn’t a setting you sprinkle on — it comes with hard restrictions, and they fail loudly:

No data compression. A sparse column and DATA_COMPRESSION are mutually exclusive on the same object — Msg 10622, “A compressed index is not supported on table that contains sparse columns or a column set column.” You pick one.
Not in a primary/clustered key, no IDENTITY/ROWGUIDCOL — Msg 1731 (a sparse column must be nullable).
No DEFAULT constraint — Msg 1791.
Plus: not text/ntext/image/geometry/geography/CLR UDT.

And two features that make sparse worth the trouble:

Column sets. Declare an XML COLUMN_SET FOR ALL_SPARSE_COLUMNS and SELECT id, cs returns the populated sparse columns as one XML blob (<a>42</a>, <b>hi</b>, and a plain NULL for an all-empty row) — the practical way to work with hundreds of sparse attributes without naming them all. This is the feature that lets a table exceed 1,024 columns.
Filtered indexes. A WHERE a1 IS NOT NULL index on a 95%-NULL sparse column stored only the 12,000 non-NULL rows in 184 KB, versus 4,192 KB / 300,000 rows for the full index — ~23× smaller. Sparse + filtered index is the pattern that makes a mostly-NULL attribute both cheap to store and fast to find when it is set.

Verdict

Sparse columns are a narrow, sharp tool — not a storage default:

Only fixed-length, only mostly-NULL. Variable-length types already store NULLs cheaply; sparse does little for them. For fixed types, know the width-driven break-even (guid/datetime2/bigint pay off around 40–45% NULL in a wide block; int ~60%; bit only above ~98%).
Measure your actual NULL% before committing. Below break-even, sparse makes the table bigger and warm reads slower — a double loss.
The real use case is wide, sparse attribute sets — the EAV / per-category catalog / mostly-empty audit columns — ideally with a column set to manage them and filtered indexes to query them. That’s where the documented thresholds are conservative and sparse earns its keep.
Remember the exclusions — no compression, no PK, no default. If you want compression on that table instead, that’s a different (and often better) bet.

The harness, the full per-type/per-NULL% storage matrix, the perf numbers, and the gotcha receipts are reproducible: spin up the SQL Server 2025 container and run ./run.sh full.