SPARSE COLUMN IN SQL SERVER 2008

SPARSE COLUMN IN SQL SERVER 2008

     Optimized Storing of Null value can be done  using SPARSE COLUMN.
     Storing a null in a sparse column takes up no space at all. . In other words, a SPARSE column is better at managing NULL and ZERO values in SQL Server. It does not occupy any space in the database.

Sample for Identifying the size using Sparse column

/*CREATE BOTH SPARSED TABLE and UNPARSED TABLE*/

CREATE TABLE UNPARSEDmark1 (
testNAME VARCHAR(100),
TESTDate smallDateTime)
GO

CREATE TABLE mark1(
testNAME VARCHAR(100) SPARSE,
TESTDate smallDateTime SPARSE)
GO

/*INSERT 45000 NULL ROWS IN BOTH TABLES*/

DECLARE @IDx INT=1
WHILE @IDx<50000
BEGIN
INSERT INTO UNPARSEDmark1 VALUES(NULL,NULL)
INSERT INTO mark1 VALUES(NULL,NULL)
SET @idx+=1
END
GO
/*CHECK THE SPACE USED BY BOTH TABLES*/
sp_spaceUsed 'UNPARSEDmark1'
GO
sp_spaceUsed 'mark1'
GO
















Advantages of SPARSE Column

• A SPARSE column saves database space when there is zero or null values in the database.
• INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
• We can get more benefit of Filtered indexes on a SPARSE column.

Limitation of SPARSE Column

•  SPARSE column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
• A SPARSE column cannot be data types like text, ntext, image, timestamp, user-defined data type, geometry, or geography.
• Merge replication does not support SPARSE columns.
• The SPARSE property of a column is not preserved when the table is copied.