List down the limitations for using SPARSE Columns

  • A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
  • A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.
  • A sparse column cannot have a default value or bound to a rule.
  • A computed column cannot be marked as SPARSE although it can contain a sparse column.
  • A sparse column cannot be part of a clustered index or a unique primary key index.
  • A sparse column cannot be used as a partition key of a clustered index or heap, but it can be used as the partition key of a nonclustered index.
  • A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.
  • Sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.
  • When a non-sparse column is changed to a sparse column, the sparse column will consume more space for non-null values. When a row is close to the maximum row size limit, the operation can fail.
  • If a table contains a sparse column, the maximum size of a row in a table will be decreased from 8060 bytes to 8012 bytes.

No comments:

Post a Comment