StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ul> <li><p>Storing a null in a sparse column takes up no space at all.</p></li> <li><p>To any external application the column will behave the same</p></li> <li><p>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.</p></li> <li><p>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.</p></li> <li><p>Change Data Capture and Transactional replication both work, but not the column sets feature.</p></li> </ul> <p><strong><em>Downsides</em></strong></p> <ul> <li><p>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.</p></li> <li><p>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)</p></li> <li><p>computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)</p></li> <li><p>You can't apply rules or have default values.</p></li> <li><p>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).</p></li> <li><p>Merge replication doesn't work.</p></li> <li><p>Data compression doesn't work.</p></li> <li><p>Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.</p></li> </ul> <p><a href="http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx" rel="noreferrer">Reference</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload