Getting more than 1024 columns in a table

The create table statement doesn't allow you to create a table with more than 1024 columns.  So how do you do it.

You have to create the table and then add them after the fact.

[21/9/2008 Thanks to Kalen for pointing out that you can if you create the table with a columnset at the same time].

Even if using column sets you have to define all the columns that you want to store. The XML columnset doesn't act like an XML overflow column, it is a computed representation of the sparse columns on the table.

The following highlights that. The first three inserts fail as they are trying to populate columns that con't exist

create table test2 (

id int not null primary key

,sp1 int sparse

,cs  xml column_set for all_sparse_columns)

go

insert into test2 (id,cs)values (1,'<col1>12123</col1>')

insert into test2 (id,cs)values (2,'<col2>aaa</col2>')

insert into test2 (id,cs)values (3,'<col3>1/1/2008</col3>')

go

select * from test2

go

declare @i int = 0

while @i<2000

begin

      declare @sql varchar(100)= 'alter table test2 add col' + cast(@i as varchar(10)) + ' int sparse'

      execute (@sql)

      set @i = @i+1

end

go

 

sp_help test2

 



-
Published 18 September 2008 15:37 by simonsabin

Comments

21 September 2008 02:52 by 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

21 September 2008 02:53 by 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

# Weekly Link Post 60 &laquo; Rhonda Tipton&#8217;s WebLog

Pingback from  Weekly Link Post 60 &laquo; Rhonda Tipton&#8217;s WebLog

24 September 2008 08:05 by Other Blogs we recommend (SSQA.net)

# 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