MS SQL Sparse Columns

Since MSSQL 2008, we can use SPARSE columns to reduce table size, in case we have frequent NULL or 0 values.
Sparse columns are not counted in the max column per table limit (1024).
(I would rather check that data model, it is not usual to have more than 80 columns in a single table)

see Code

Benefits:

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.

Limitations:

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.

see Code

Deja un comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.