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.



-
Published 16 March 2008 21:21 by simonsabin

Comments

# sql select column value by column number

Pingback from  sql select column value by column number

# DBMS2 — DataBase Management System Services » Blog Archive » XML versus sparse columns in variable schemas

Pingback from  DBMS2 — DataBase Management System Services  » Blog Archive   » XML versus sparse columns in variable schemas