As I am working on my new SQL Server 2008 internals book, I am finding many test situations in which I need to create a table with LOTS of columns. First of all, you're probably aware of the new SPARSE column feature that allows you to have up to 30,000 columns in a table!  I did some testing with sparse columns, but just today I realized I had never actually created a table with more than the old limit of 1024.  In addition, the new row compression had some special tricks for dealing with lots of columns, and groups columns into clusters of 30 columns each. (You'll have to wait for the new book to get the details.)

Yesterday, when writing about row compression, I realized I needed an easier way to create wide tables, so I wrote a script that allowed me to specify the number of columns, and also get a little creative with the datatypes, lengths and properties of the columns I was creating. I'll show you that script in just a minute!

Then just today, I read a blog entry by Simon Sabin, who indicated that even with sparse columns, you could not create a table with more than 1024 columns. In the CREATE TABLE statement, you had to limit yourself, but then could use ALTER TABLE to add more columns. Although I hadn't tested this yet, I was intrigued, and realized I could use my new script to test out this claim.

So here is my original script. You can replace the 100 (for the value of @numcols) by the number of columns you'd like, but the script will create one more, because it starts with a ID int identity column. So the script as is will create a table with 11 columns. In the loop that adds columns to the creation string, I had three different datatypes of columns that I can use. One third of the columns will be type int with a default of 0, one third will be char(5) with a default of 'hello' and one third will be varchar(25) with a default of 'this is a longer message'. You can modify this however you like. You can change the datatypes within the loop, or add more conditionals and change expression to use modulo of some other number.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 100;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
      ' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0,';
    IF (@col % 3) = 1    
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) default ''hello'',';
     IF (@col % 3) = 2     
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) default ''this is a longer message'',';
  SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create 
EXECUTE (@create) 
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);

To use this script to test the creation of a table with more than 1024 columns, I knew some (or most?) of the columns would need to be sparse. In addition, I found this Connect entry which indicated that if you do have more than 1024 columns, the table must have a column set, so it was easy enough to make that the last column that is added to the create string after the loop.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 1200;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
      ' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int sparse,';
    IF (@col % 3) = 1    
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(5) sparse,';
     IF (@col % 3) = 2     
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) sparse,';
       SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' xml column_set for all_sparse_columns);';
PRINT @create 
EXECUTE (@create) 
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);
 

Running this script, I was able to create a table with 1200 columns.

And you can too, if you're using SQL Server 2008.

Have fun!

~Kalen