* 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
* computed columns can't be sparse (although sparse columns can take part in a
calculation in another computed column)
* We can't apply rules or have default values.
* Sparse columns cannot form part of a clustered index. If we 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.
Asked In: Many Interviews |