I have been writing recently about how SQL Server 2008 is better in terms of Data Stage and Backup Management. I have received very good replies from many users and have requested to write more about it. Today we will look into another interesting concept of SPARSE column. The reason I like this feature because it is way better in terms of how columns are managed in SQL Server.
SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).
In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.
Let us see following example of how SPARSE column saves space in database table.
CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO
UPDATE : Read Advantages and Disadvantages of SPARSE Column SQL SERVER - 2008 - Introduction to SPARSE Columns - Part 2
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
This is very shallow introduction. So what is the disadvantage?
[...] 14, 2008 by pinaldave Previously I wrote about SQL SERVER - 2008 - Introduction to SPARSE Columns. Let us understand the concept of SPARSE column in more detail. I suggest you read the first part [...]
Hi…
I am not quite sure if sparse columns are that great. I mean SQL2008 does come with an option to compress row data.
How would this feature work combined with sparse Cols?
And I dont think the cost of diskspace is that high these days. You could save a few bytes here and there, but what would you really gain? You can have more columns per row, but I see 1024 and think do we realy need more in a realtional DB? I can see more in a cube, but then most columns would store redundant data,and row compression should be very efficient.
You mention a performance hit? How big is it? That would be much more interesting.
This is very small introduction, plz give more details
The row compression and page compression do compress Null value. The compression ratio of sparse is usually less than the page compression. However, Sparse has less CPU overhead than row compress and page compression because it does not compress the not null value, but only ignore the Null values. I will post an example of comparing the storage size and the performance different for these two techniques.