What is SPARSE Column in MS SQL SERVER?

Introduced in SQL Server 2008, The SPARSE column is better at managing database size when null values are plenty in that particular column. This is lesser know column type but very useful when you expect null records in particular record.

To declare a particular column as sparse we have to simply specify the keyword ‘sparse’ after declaring the column name and the data type.

 Please refer to the below mentioned queries for a better understanding of sparse column and its use.

CREATE TABLE UnsparsedTable
(ID INT IDENTITY(1, 1),
Col1 INT,
Col2 VARCHAR(100),
Col3 SMALLDATETIME,
Col4 UNIQUEIDENTIFIER
);
GO
CREATE TABLE SparsedTable
(ID INT IDENTITY(1, 1),
Col1 INT SPARSE,
Col2 VARCHAR(100) SPARSE,
Col3 SMALLDATETIME SPARSE,
Col4 UNIQUEIDENTIFIER SPARSE
);
GO
DECLARE @ctr INT= 0;
WHILE @ctr < 90000
BEGIN
INSERT INTO UnsparsedTable
VALUES (NULL, NULL, NULL, NULL );
INSERT INTO SparsedTable
VALUES (NULL, NULL, NULL, NULL );
SET @ctr+=1;
END;
GO
sp_spaceused
'UnsparsedTable';
GO
sp_spaceused
'SparsedTable';
GO
DROP TABLE UnsparsedTable;
GO
DROP TABLE SparsedTable;
GO

You can see here that the space occupied by null values sparse columns is far less than the regular column with null value.

Post your comments about this.