What are some of the disadvantages of using SPARSE column?

 Posted by Niladri.Biswas on 6/12/2013 | Category: Sql Server Interview questions | Views: 3311 | Points: 40

* 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 | Alert Moderator 

Comments or Responses

Login to post response