SQL Server 2008 - Whats this abut 30,000 columns?
Its all about Sparse columns and they are a great
feature of Katmai
Imagine you have a products table, you will have attributes for some products
that don't apply to others i.e. cup size applies to bras but not to socks.
There are a number of ways of modelling this each with downsides. With sparse
columns you can have a column per attribute per product. The benefit is that you
are following proper modelling by not mixing meaning of a column and you have
strongly type data. The other aspect is that you save space because sparse
columns are stored in a similar fashion to variable length fields. Thus if the
value is NULL you will save space especially on fixed type columns.
I can imagine the select and insert limits haven't been increased because you
also have the ability of having a column set which combines all the sparse
columns into an XML document containing them. You are abe to select this (if you
do a select * and you have this you will only get this not the individual
columns) but you are also able to update and insert through it.
Aaron recently posted on the increase in the column limits, 30,000 sparse
columns are now possible. (http://sqlblog.com/blogs/aaron_bertrand/archive/2008/03/11/increased-limits-for-columns-indexes-statistics.aspx)
He also asks why. Well this is it.