SQL Interview: 41 Sparse columns vs Nullable columns

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administrator Level: Medium

Question:

Your team is considering a design where a small number of tables would each have a very large number of columns.

One developer has suggested making the columns SPARSE where another has suggested just making them NULLable.

What difference would there be in a SPARSE column vs just a NULLable column.

Answer:

SPARSE columns occupy no space at all in any rows where they do not contain any data.

By comparison, a NULLable column occupies at least one bit for each column in each row.

2025-04-28