Maqsood Ahmad (10/23/2008)
Thanks for your prompt reply
I understand that in SQL Server 2005 we can't update it but because we have migrated our application from SQL Server 2000 to 2005 and in 2000 we were able to change these numbers. everthing was fine in 2000.
Maybe, maybe not. Run DBCC CHECKDB('< Database Name > ') WITH NO_INFOMSGS on that db and see what errors checkDb returns. In 2000 CheckDB disn't check the metadata. In 2005 it does. You may already have serious problems with that DB that you're not yet aware of.
What i think that if we delete a column from any table SQL Server should update these number as well.
Why? The colid isn't a meaningful number. It's just an artificial key - a number that the DB engine can use to reference the column elsewhere in the metadata
Now its hard to change whole application layer at this stage
Even in SQL 2000 there were warnings and cautions about changing the system tables. In fact MSDN states:
Caution Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services. Because system tables are critical to the operation of SQL Server, enable allow updates only in tightly controlled situations.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass