SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

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 before continuing reading this article.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

  • INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
  • SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
  • SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

  • SPARSE column does not have IDENTITY or ROWGUIDCOL property.
  • SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
  • SPARSE column can not have default value or rule or computed column.
  • Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
  • Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
  • A table operation which involves SPARSE column takes performance hit over regular column.

Let me know your thoughts about SPARSE column feature of SQL Server 2008, I am very eager to see your point of view on this new feature.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – 2008 – Introduction to SPARSE Columns

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://blog.SQLAuthority.com)