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