Working with SPARSE column in SQL Server 2008

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

I hope you liked this post of new feature of SQL Server 2008.