BT

Sparse Columns Added to SQL Server 2008

| by Jonathan Allen Follow 634 Followers on Jun 26, 2007. Estimated reading time: 1 minute |

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

Adoption Stage
Style

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.

Tell us what you think

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

Email me replies to any of my messages in this thread
Community comments

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

Email me replies to any of my messages in this thread

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

Email me replies to any of my messages in this thread

Discuss
BT