Geek City: Too Many Columns!

re: Geek City: Too Many Columns!

IL — Sun, 21 Sep 2008 07:57:21 GMT

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?

re: Geek City: Too Many Columns!

Kalen Delaney — Mon, 22 Sep 2008 05:30:52 GMT

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

re: Geek City: Too Many Columns!

Bob — Mon, 22 Sep 2008 16:20:34 GMT

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

re: Geek City: Too Many Columns!

Kalen Delaney — Mon, 22 Sep 2008 16:26:38 GMT

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

re: Geek City: Too Many Columns!

Kalen Delaney — Mon, 22 Sep 2008 17:47:55 GMT

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

re: Geek City: Too Many Columns!

Mike — Fri, 10 Oct 2008 17:55:11 GMT

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.

Geek City: Too Many Indexes!

Kalen Delaney — Sun, 18 Jan 2009 20:37:49 GMT

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

re: Geek City: Too Many Columns!

Adam — Tue, 09 Feb 2010 18:41:04 GMT

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?

re: Geek City: Too Many Columns!

Adam — Tue, 23 Feb 2010 07:27:15 GMT

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