Facilitating the spread of knowledge and innovation in professional software development



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...)


We need your feedback

How might we improve InfoQ for you

Thank you for being an InfoQ reader.

Each year, we seek feedback from our readers to help us improve InfoQ. Would you mind spending 2 minutes to share your feedback in our short survey? Your feedback will directly help us continually evolve how we support you.

Take the Survey

Rate this Article


Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p


Is your profile up-to-date? Please take a moment to review and update.

Note: If updating/changing your email, a validation request will be sent

Company name:
Company role:
Company size:
You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.