What is Sparse columns in SQL Server?

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).



In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.



Let us see following example of how SPARSE column saves space in database table.

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO

---

Many Thanks & Best Regards, Hua Min