What is the difference between Wide and Nonwide tables in SQL 2008?

Question

I was looking at this page on MSDN:

Maximum Capacity Specifications for SQL Server 2008

And it says the following:

Max Columns per 'nonwide' table: 1,024
Max Columns per 'wide' table: 30,000

However I cannot find any information on the difference between 'wide' and 'nonwide' tables in SQL 2008. If I wanted to define a 'wide' table, how would I do it?

Answer 1

Special Table Types

All the info you need is in this MSDN article.

A wide table is just a table with sparse columns. To make a table wide you just add a column set to its definition.

Answer 2

I would say the difference is about 28,976 columns.

Answer 3

It is important to note that your total fixed and variable length data are still limited to 8019 bytes total. Being able to do this crazy extra large number of columns is only supported in sparse tables where MOST of the data is nulls. Otherwise you still end up with rows that exceed the 8019 bytes and end up with rowdata that won't fit, or overflow into extended row data (which is very expensive to maintain compared to normal data pages).

There is a really good book from Karen Delaney that has a ton of internal features and limits for SQL Server entitled SQL Server 2008 Internals. If you are really into the low level limits and how things are done in SQL Server it is a fantastic read. It will increase the depth of your knowledge for how SQL Server does what it does under the hood at the byte level to disk in some cases.