Add identity column to an non-identity integer column in SQL Server 2005

Question

Hi, I know how to do this and moreover this has been asked by many peoples in STACK OVER as well as bunch & bunch of stuffs are available in the net.

However, while googling, I came across something interesting (at least for me) adding IDENTITY to existing column

The solution to the above question (obtained from that site, at the bottom end of the page) is like

sp_configure 'allow update', 1
go
reconfigure with override
go
 update syscolumns set colstat = 1
where id = object_id('table1') and name = 'ID'
go
 exec sp_configure 'allow update', 0
go
reconfigure with override
go

So I gave a shot with the appropriate table and column names of mine and found the following

Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

What I am doing wrong?

Please help

Answer 1

That code sample, particularly the table "syscolumns", sounds like it might work in SQL 2000, and maybe 7.0. In those versions, the system tables could be modified directly using the process shown. (Late one night long ago, a friend and I dropped the SA account on a machine that was being rebuilt the next day...)

In SQL 2005, Microsoft revised SQL's internals so that the system tables were no longer directly accessible or hackable. The various "sys.XXX" system tables are actually views on the real tables, and those tables cannot be directly modified. (Acutally, I suspect the dedicated hacker can manage something, but I haven't tried yet.)

"syscolumns" still exists in SQL 2005 and up, but it's probably just a view on sys.columns.

Answer 2

Quote from Micheal Hotek:

For over 15 years, Microsoft has been telling everyone to NOT directly update system tables and that the functionality would be removed at a later date. You can NOT do this in Oracle or DB2. It is an extraordinarily bad idea and incredibly bad practice. In SQL Server 2000, there were a miniscule number of cases where direct updates had to be done, because there was no other way. (There isn't a single example in this thread which would have required direct system table modification in SQL Server 2000 and each case offer thus far for screwing with the system data is a result of bad code meeting bad practices.) Removing the ability to directly screw around with data in system tables is a USER request and I'm one of those users who will be screaming if anyone decides that it is a good idea to bring back the ability of someone to completely blow up an instance with the click of a button. Rewriting the system catalogs as been and always will be an extraordinarily BAD idea.

In order to do what you want to do, you have to create a new table with identity column, copy your old values into the new table, delete the old table and rename the new one. An example can be found here

Obviously this will be all the more painful if your table is subject to foreign key constraints which would have to be deleted and reestablished as well.

Answer 3

Well, you can't - period. At least not in SQL Server 2005 and up.

What you need to do is add a new column as INT IDENTITY, drop the old column, and then rename the new one to the old name.

It's a bit involved - but it works, and works reliably.

ALTER TABLE dbo.YourTable
  ADD NewColumn INT IDENTITY(1,1) 

ALTER TABLE dbo.YourTable
  DROP COLUMN OldColumn

EXEC sys.sp_rename @objname = 'dbo.YourTable.NewColumn', 
                   @newname = 'OldColumn', 
                   @objtype = 'COLUMN'