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

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 356932 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/18/2008 1:45:34 PM
Access Restriction Public


I cannot create more than 1024 Sparse columns to a table.
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:

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
SQL Server Engine Development