SPARSE COLUMN-SQL SERVER 2008
SQL SERVER 2008's new feature SPARSE COLUMN. Sparse columns are ordinary
columns that have an optimized storage for null values. Sparse columns reduce
the space requirements for null values at the cost of more overhead to retrieve
nonnull values. Consider using sparse columns when the space saved is at least
20 percent to 40 percent. Sparse columns and column sets are defined by using
the CREATE TABLE or ALTER TABLE statements.
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.
- A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that's still a net savings.
- Storing a null in a sparse column takes up no space at all.
- To any external application the column will behave the same
- Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
- 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.
- Change Data Capture and Transactional replication both work, but not the column sets feature.
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.
- If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
- Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse. (Changed17/5/2009 thanks Alex for spotting the typo)
- computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
- You can't apply rules or have default values.
- Sparse columns cannot form part of a clustered index. If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
- Merge replication doesn't work.
- Data compression doesn't work.
- Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.
·
Syntax
to create sparsed columns
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
