Recently during migration from SQL server 2005 to SQL Server 2008, I have utilized one more feature of SQL server 2008 and that is SPARSE column. It is used to reduce storage data in tables, mostly when tables having 20-30 % NULL values.
It is really amazing feature and we can reduce storage space as we can with SPARSE. Here are some demonstration with example.
Creating database and table objects
-- Creating database
CREATE DATABASE SparseDB
GO
USE SparseDB
GO
-- Creating table without sparse column
CREATE TABLE TablewithoutSparse
(
Id int identity(1,1),
SparseCol1 int,
SparseCol2 varchar(100),
SparseCol3 datetime
)
GO
-- Creating table with sparse column
CREATE TABLE TablewithSparse
(
Id int identity(1,1),
SparseCo1l int SPARSE,
SparseCol2 varchar(100) SPARSE,
SparseCol3 datetime SPARSE
)
GO
Let us insert some records in tables.
-- Inserting 55555 records (All NULL values) in both tables
INSERT INTO TablewithoutSparse
VALUES (NULL, NULL, NULL)
INSERT INTO TablewithSparse
VALUES (NULL, NULL, NULL)
GO 55555
Now finaly checking the result.
-- Checking rows and space used by both tables
EXEC SP_SPACEUSED TablewithoutSparse
GO
EXEC SP_SPACEUSED TablewithSparse
GO