Looking into SPARSE Columns in SQL Server April 19, 2012Posted by fofo in Sql Server, SQL Server 2008.
Tags: sparse columns
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!!!!