Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News Sparse Columns Added to SQL Server 2008

Sparse Columns Added to SQL Server 2008

SQL Server 2008 has lifted the limit of 1024 columns per table with a new option called "sparse columns". While this seems like its excessive, some developers have been running up against this limit.

A possible use for this would be a contact management system where a lot of information can be applied to a single individual. Often this information is only applicable to a small subset of the users, potentially wasting a lot of space. A common solution to this problem is to create an "Entity-attribute-value" table with a column for contact ids, property names or keys, and values.

EAV tables do have problems though. For one, they grow much faster than normal tables and thus can cause some serious performance issues. Another problem is that they are not strictly relational. Converting an EVA table to a normal table is time consuming, especially before SQL Server 2005 introduced the PIVOT keyword.

With sparse columns, both the limit on the number of columns and the associated space requirements are eliminated. According to Steve Jones, SQL Server 2008 is supposed to support literally "hundreds of thousands of sparse columns". Unfortunately, details on this seem rather thin at the moment.

Bob Beauchemin does have an example of the new syntax posted:

CREATE TABLE products (product_num int, item_num int, price decimal(7,2), ...,
color char(5) SPARSE, width float SPARSE...)


Rate this Article