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.


0
0
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

Description

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:

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