When I try to add a column to MY_TABLE, the ALTER TABLE statement fails with the following error:Peter Yang:
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?
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.Me:
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 GOTo 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.
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.
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')
My suggestion submitted to Microsoft - if you have this problem too, rate it!