Creating Complex Test Databases - Creating a Table with more than 1,024 Columns

As part of my series on creating databases with interesting characteristics for testing purposes, today we'll cover a question that was asked in the SQL Server MSDN forums regarding column limits. 

The question is "What is the true column limit in SQL Server?"

From a testing perspective, one of the more interesting documentation articles is the Maximum Capacity Specifications for SQL Server, which lists the maximum size of various database objects.  This is interesting from a test perspective because this value acts as our upper bound during object creation/definition.  Ex:  The maximum number of supported columns per primary and foreign key is 16.  If your application accesses this meta-data, have you tested out your app using 16 columns in a PK/FK?  There are many other interesting limits to explore depending on the nature of your application and database needs.  The SQL Server engine will prevent you from creating a 17th column as part of a primary key, in this example, but many applications make assumptions regarding data and meta-data that lead to ugly bugs.  Knowing these limits and testing with these values will prevent these bugs from reaching your customers.

But let's get back to the question at hand.  The specifications list the following values for maximum columns supported per table:

Columns per non wide table:  1,024
Columns per wide table:         30,000

At this point, we need to define the term "wide table".  A wide table in SQL Server is a table that has defined a column set.  Similarly, a non-wide table is a table that has no defined column set.

Looking first at the "non wide" table, we see that the engine will prevent us from exceeding the limit of 1,024 columns.  Attempting to run a CREATE TABLE statement with 1,025 or more columns will result in the following engine error:

Msg 1702, Level 16, State 2, Line 1

CREATE TABLE failed because column 'abc_1025' in
table 'non_wide_table' exceeds the maximum of 1024 columns. 

However, by leveraging the column set functionality, we can create a table with greater than 1,024 columns.  The AdventureWorks example from Books Online shows a nice example, where the column SpecialPurposeColumns (highlighted in blue) is defined as the column set that returns the 4 sparse columns (highlighted in green) defined in the table definition :

USE AdventureWorks;
GO

CREATE TABLE DocumentStoreWithColumnSet
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL,
MarketingProgramID int SPARSE NULL,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
GO

As we've seen, the SQL Server engine will prevent you from exceeding the supported limit for table columns, but by leveraging the column set and sparse constructs, we can extend our CREATE TABLE statement to use up to 30,000 columns. 

Hope you enjoy,
Sam Lester (MSFT)