Home Dashboard Directory Help
Search

Cannot add more than 1024 Sparse columns to user table. by jpatterson4


Status: 

Closed
 as Fixed Help for as Fixed


0
0
Sign in
to vote
Type: Bug
ID: 356932
Opened: 7/18/2008 1:45:34 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

I cannot create more than 1024 Sparse columns to a table.
Details
Sign in to post a comment.
Posted by Microsoft on 8/7/2008 at 11:39 AM
Hello Jay,

Thank you for your comments on the sparse columns entry in the create table topic in SQL Server Books Online. In order for a table to have more than 1024 columns the table must contain a column set. The topic has been updated and will be available in a future version of SQL Server Books Online.

-Kelly Wilson
SQL Server Team
Posted by AaronBertrand on 7/19/2008 at 9:45 AM
Funny, I seem to remember doing this on the previous CTP without declaring a column set. Funny too that the error message doesn't change if you do:

SET @SQL = 'CREATE TABLE MaxSparseColumnTest (a INT,b INT,c INT,d INT,e INT,f INT,'

Or even:

SET @SQL = 'CREATE TABLE MaxSparseColumnTest (a INT,b INT,c INT,d INT,e INT, f INT SPARSE NULL,'
Posted by Microsoft on 7/18/2008 at 4:14 PM
Thank you for the report.

The creation of more than 1024 columns in a table requires the use of a column_set column. Without a column_set the limit is 1024 columns total. With a column_set, the limits are 1024 non-sparse columns (including the column_set and computed columns) and 30,000 columns total. These limits are mentioned in the CREATE TABLE topic in Books Online, but the column_set portion appears to be missing.

If you change the prefix in your dynamically built CREATE like this, it should allow the table creation to work properly:
SET @SQL = 'CREATE TABLE MaxSparseColumnTest (cs xml column_set for all_sparse_columns, '

Hope that helps

Jerry Weiler
jweiler@microsoft.com
SQL Server Engine Development
Sign in to post a workaround.