jump to navigation

Looking into SPARSE Columns in SQL Server April 19, 2012

Posted by fofo in Sql Server, SQL Server 2008.
Tags:
trackback

In this rather short post I would like to provide you with a hands-on example on SPARSE columns. SPARSE columns are very useful when we have tables and columns with many zero and NULL values.This feature was introduced in SQL Server 2008.We have a better, more efficient way to manage empty space through SPARSE columns by enabling NULL data to consume no space at all.

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition,  here.

Launch SSMS and connect to an instance of SQL Server. In a new query window type the following T-SQL statements and execute them


USE tempdb
GO

CREATE TABLE tblUnsparsed
(
ID INT IDENTITY(1, 1) ,
OrderID  INT ,
Size VARCHAR(100) ,
OrderDate DATETIME
)
GO

CREATE TABLE tblSparsed
(
ID INT IDENTITY(1, 1) ,
OrderID  INT SPARSE ,
Size VARCHAR(100) SPARSE ,
OrderDate DATETIME SPARSE
)
GO

INSERT INTO tblUnsparsed VALUES (NULL,NULL, NULL)
GO 200000

INSERT INTO tblSparsed VALUES (NULL, NULL, NULL)
GO 200000

SELECT * FROM tblUnsparsed

SELECT * FROM tblSparsed

sp_spaceused 'tblUnsparsed'
GO
sp_spaceused 'tblSparsed'
GO

DROP TABLE tblSparsed
GO
DROP TABLE tblUnsparsed
GO

I create two tables.The second table has the SPARSE statement in the column definition.I insert 200.000 rows of NULL values in both of the tables.Then I check to see if the rows were inserted.200.000 rows were inserted in both tables.Then I use the sp_spaceused system stored procedure to find out how much space was consumed.

You will see a big difference when SQL Server returns the results.

Have a look in the picture below to see what I got

We cannot apply the SPARSE statement to columns where their data types are :image,text,geography,geometry.We cannot apply rules or defaults to SPARSE columns. We cannot have Data Compression and Merge replication does not work either.In case the SPARSE columns have data in them they will take 4 more bytes than the normal column. So please be careful when you use SPARSE columns.

Hope it helps!!!!

Advertisements

Comments»

1. Dot Net Rules : Looking into SPARSE Columns in SQL Server - April 19, 2012

[…] way to manage empty space through SPARSE columns by enabling NULL data to consume no space at all.(read more) Share Posted: Πέμπτη, 19 Απριλίου 2012 2:10 πμ από το μέλος […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: