THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Too Many Columns!

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

Published Saturday, September 20, 2008 7:52 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

IL said:

It seems to be 1023 columns could be made ordinary and starting from 1024 have to belong sparse column set. I've modified last script like this:

DECLARE @create varchar(max);

DECLARE @tabname sysname;

DECLARE @numcols int;

DECLARE @col int;

SELECT @numcols = 2000;

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';

   IF (@col % 3) = 1    

      SELECT @create = @create + 'col' + CONVERT(varchar, @col) +

          ' char(5)';

   IF (@col % 3) = 2    

      SELECT @create = @create + 'col' + CONVERT(varchar, @col) +

          ' varchar(25)';

   IF @col > 1022 SELECT @create = @create + ' sparse,'

   else SELECT @create = @create +  ','

   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);

No default values are allowed on sparse columns. Why?

September 21, 2008 3:57 AM
 

Kalen Delaney said:

Hi IL

There is a default for sparse columns, it is NULL. The whole idea of sparse columns is that MOST of the rows will have a NULL value. If you want some other value to be the default and not have NULLs, that is not what sparse columns are for and you should re-evaluate your design.

~Kalen

September 22, 2008 1:30 AM
 

Bob said:

I shudder to think what you're doing that would require more than 1,000 columns, much less 30,000!

September 22, 2008 12:20 PM
 

Kalen Delaney said:

I Bob... all I'm doing is creating a table! I always tell my students that 1000 (or 1024) columns is WAY too many. But since Microsoft allows very wide tables in 2008, my job is to tell people how it works.

~Kalen

September 22, 2008 12:26 PM
 

Kalen Delaney said:

Bob....

I just realized I actually expressed my opinion of having so many columns in this interview at http://www.simple-talk.com/opinion/geek-of-the-week/kalen-delaney-geek-of-the-week/...

Also, the name of this post is TOO MANY Columns...

:-)

~Kalen

September 22, 2008 1:47 PM
 

Mike said:

While you might point to a 1000+ column table and say I can design that better using 10 columns and 2 or 3 tables, when it's a small cog in a much larger wheel of half closed and half open software that feed off the same table automatically, the 1000+ column table, on occasion, can sure come in handy for me.

October 10, 2008 1:55 PM
 

Kalen Delaney said:

I wrote about "Too Many Columns" last September, and along with changes in SQL Server 2008 that allow

January 18, 2009 3:37 PM
 

Adam said:

Hi Kalen, it's strange how things happen. I am currently working with a client who has a mainframe system table which contains 6,000 columns, yes 6000!

The requirement is to extract this data into SQL Server like for like. They are running SQL Server 2005 currently so I have split the data into 4 logical tables but they are moving to 2008 very soon so I wanted to know how viable this option would be?

The vast majority of these fields actually contain data with no nulls would you still recommend using sparse columns for this solution, and would it work in the given scenario?

February 9, 2010 1:41 PM
 

Adam said:

Thanks Kalen,

I thought I would come back with a quick update. The sparse columns option wasn't really viable as I wss reaching the row size limit in bytes before I got to the 6k columns. I stuck with the multiple table approach and this is working well.

Adam

February 23, 2010 2:27 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement