SPARSE Columns in Sqlserver.

Hi All, Sparse column is a new concept introduced in Sql server 2008 onwards. 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).

You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set. The column set behaves like a column itself. Note: you can only have one column set per table.

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.

Advantages of Sparse Columns:
1. If the value of a column is NULL, it doesn’t consume space at all.
2. Support of having 30000 sparse columns in a table.
3. It stores the data in a single xml column but for an external application it behaves like a normal column.
4. SPARSE column can take advantage of filtered Indexes, where data are filled in the row.

Limitations:
1. All the data types cannot be sparse. Text, NText,Geometry, Geography, timestamp, user defined datatypes, varbinary(max), filestream attribute column.
2. Sparse Column doesn’t have IDENTITY or ROWGUIDCOL Property
3. Sparse Column cannot have a default value or rule or computed column.
4. Sparse column cannot be party of clustered index key. Also it cannot be added as an indexed column for unique index as well.
5. The maximum size of a row in a table will be decreased from 8060 bytes to 8012 bytes if a table contains a sparse column.

CREATE TABLE tblUnSparsed(ID INT IDENTITY(1,1),
Name NVARCHAR(100),
ContactNo NVARCHAR(10),
Address NVARCHAR(500))
GO
CREATE TABLE tblSparsed(ID INT IDENTITY(1,1),
Name NVARCHAR(100),
ContactNo NVARCHAR(10),
Address NVARCHAR(500))
GO
DECLARE @count INT = 0
WHILE @count < 10000
BEGIN
INSERT INTO tblUnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO tblSparsed VALUES (NULL, NULL, NULL)
SET @count += 1
END
GO
sp_spaceused ‘tblUnSparsed’
GO
sp_spaceused ‘tblSparsed’
GO
DROP TABLE tblUnSparsed,tblSparsed
GO

1

Advertisements

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