Wide tables in SQL Server 2008

SQL Server table supports up to 1024 columns per table which is typically good enough for most of the cases. However if you need to expand a table beyond 1024 columns then you need to use a special table types named as Wide Tables. Wide tables are tables which can have more than 1024 columns and can actually have up to 30,000 columns. For in depth details on wide table, you may want refer http://msdn.microsoft.com/en-us/library/ms186986.aspx

In this post I will discuss using wide tables in practical life without diving deep into the architecture:

Should I use/encourage using wide tables?

Simple Answer to this is No. Wide table uses sparse columns to increase the total of columns. This technology creates issues like performance problems, complications with index management, limits execution of DML statements, reduce performance for switch partition operations, prevents using compression etc. Hence unless absolutely necessary, it is not advisable to use wide tables. You may recommend using other technologies like joins or xml instead of using wide tables.

For more details on possible performance consideration for wide tables please refer to http://msdn.microsoft.com/en-us/library/cc645884.aspx

How to create a wide table? (How to create a table which will have more than 1024 columns?)

Step 1:

Create a column set on the table using below code:

[Note: This must be first step before adding any sparse column to the table. If the table already has any sparse column then you cannot add column set. Also if you cannot add a column set then your table can not have more than 1024 columns even if you attempt to add new columns as sparse column]

ALTER TABLE [table name]

ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;

GO

Example:

ALTER TABLE dbo.table1

ADD Widecolumn1 varchar(10) SPARSE NULL ;

GO

Step 2:

Now you can add columns and widen the table as you need.

[Note: New columns must be sparse columns assuming you already have 1024 traditional (non-sparse) columns. You cannot add more than 1024 non-sparse columns even if the table has column set.]

ALTER TABLE [table name]

ADD [Column Name] [Datatype] SPARSE NULL ;

GO

Example:

ALTER TABLE dbo.table1

ADD Widecolumn1 varchar(10) SPARSE NULL ;

GO

What I need to know while using sparse column?

Please refer to http://msdn.microsoft.com/en-us/library/cc280604.aspx for details. However below tips are useful for immediate references:

1. geography, text, geometry, timestamp, image, user-defined data types, ntext cannot be used as a sparse column data type.
2. SELECT…INTO statement does not copy over the sparse column property into a new table.
3. A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties
4. A sparse column cannot have a default value or cannot be bound to a rule.
5. A sparse column cannot be part of a clustered index or a unique primary key index.
6. Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE

How to use data (read/write/alter) from a wide table?

You can use traditional statements to read/write/alter data in a wide table. However you should prefer using column set for data modification or retrieval for all sparse columns in a wide table instead of using traditional statements. Please refer to http://msdn.microsoft.com/en-us/library/cc280521.aspx for details on using column set.