Why SPARSE column ?


Sparse Column:
Sparse column is like a normal column that has optimized storage for NULL.

Sparse column reduces the space requirement for NULL.

If the column value is NULL then, the values require NO STORAGE.

Using Normal Column:
IF OBJECT_ID('Tb_NormalColumn','U') IS NOT NULL
DROP TABLE Tb_NormalColumn
GO
CREATE TABLE Tb_NormalColumn
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(100) NULL,
Column2 VARCHAR(100) NULL
)
GO

INSERT Tb_NormalColumn(Column2) VALUES('www.sqlserverbuddy.blogspot.com')
GO 1000


Here, The column1 uses NULL and It consumes some spaces in allocation.

sp_spaceused Tb_NormalColumn
GO



Using SPARSE Column:
IF OBJECT_ID('Tb_SparseColumn','U') IS NOT NULL
DROP TABLE Tb_SparseColumn
GO
CREATE TABLE Tb_SparseColumn
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(100) SPARSE NULL,
Column2 VARCHAR(100) NULL
)
GO

INSERT Tb_SparseColumn(Column2) VALUES('www.sqlserverbuddy.blogspot.com')
GO 1000


Here, The column1 uses NULL and It consumes some spaces in allocation.

sp_spaceused Tb_SparseColumn
GO



To identity the SPARSE column:
SELECT name,is_sparse FROM sys.columns WHERE [object_id] = object_id('Tb_SparseColumn')
GO


Limitation of using SPARSE column:
1. SPARSE column should be Nullable.
2. It can not have RowGuidCol or Identity property and Filestream attribute.
3. It can not be Text, nText, Image, Timestamp, Geometry, Geography & User defined data type.
4. It can not have Default value, Computed columns.
5. It can not be a part of Clustered Index or Primary Key.
6. Normally, a row can have maximum of 8060 bytes. But, when using SPARSE column, row size will be 8018 bytes only.
7. When changing a normal column to a SPARSE column, the SPARSE column will consume more space for nonnull values than the normal columns.

Note: This feature NOT available prior to SQL Server 2008.

Conclusion:
Using SPARSE column, we can avoide/reduce memory allocation for NULL values

No comments:

Post a Comment