'Alter table failed because unique column IDs have been exhausted for table MY_TABLE' error

Below you'll find a excerpts from my conversation with Peter Yang from Microsoft in microsoft.private.directaccess.sqlserver newsgroup.

Me:

When I try to add a column to MY_TABLE, the ALTER TABLE statement fails with the following error:

Alter table failed because unique column IDs have been exhausted for table 'MY_TABLE'.

1. Can you please explain this exactly means?
2. What is the 'column ID'? (I suppose it is some column in some system table)
3. Is there any way to solve this problem apart from dropping and re-creating the table?

Peter Yang:

Since column id is not reused and the server also defines the maximum number of column id (MAXCOLID == 32000), then trying to alter table add column, alter table drop column until beyond 32000 iteration will be failed. You shall see the error message you encounter under the situation.

For example, using the following script shall reproduce the problem:

CREATE TABLE T1 (C int, C0 varchar)
GO

CREATE CLUSTERED INDEX IDX1 ON T1(C)
GO

DECLARE @i int
SET @i = 1
WHILE @i <= 32770
BEGIN
IF @i % 2 = 1
BEGIN
ALTER TABLE T1 ADD C1 varchar   
ALTER TABLE T1 DROP COLUMN C0    
END    
ELSE   
BEGIN   
ALTER TABLE T1 ADD C0 varchar   
ALTER TABLE T1 DROP COLUMN C1  
END  
SET @i = @i + 1
END 
GO 

DROP TABLE T1
GO

To work around the issue, you may need to create a new table with the columns you want other than alter the table since it has reached maximum ids of the server.

Me:

I just to want to make sure that re-creating the entire table is the only way to solve this problem. There is no way to reset the column id value, is there?

Peter Yang:

Thank you for your reply. Based on my research, there is no method to reset columnID value for a table and columnIds are not reused. This is by design behavior and hard coded right now.

UPDATE:

I am not the only one who encountered this problem. A guy from work showed how to determine the maximum used COLUMN_ID for a chosen table.

-- SQL Server 2000
SELECT 
name AS TABLE_NAME
, info AS MAX_COLUMN_ID_USED
FROM sysobjects
WHERE id = OBJECT_ID('put_the_table_name_here')

-- SQL Server 2005
SELECT 
name AS TABLE_NAME
, max_column_id_used AS MAX_COLUMN_ID_USED
FROM sys.tables
WHERE object_id = OBJECT_ID('put_the_table_name_here')

UPDATE 2:

My suggestion submitted to Microsoft - if you have this problem too, rate it!