Identity Column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Identity Column

We have a table (Table A) which has more than 70 million rows. This table is replicated to another server with table name (Table B) with same number of rows but with 2 extra columns. One of the extra column added is the "identity column". This publication was out of sync, so we have to sync the table again and create the extra 2 columns (on Table B). Now we have to update "identity" column values as well on this table as we have reports and other processes using this value. I know we can insert values in "identity" column but how can we update values in identity column. I tried doing this by adding another column and updating that column values. Since this table is quiet big, so whenever I try to change this column as "identity column" through Enterprise manager, this process times out?? Can anyone please suggest any solution to this urgently?? Thanks
Read in BOL about
SET IDENTITY_INSERT dbo.tablename ON
SET IDENTITY_INSERT dbo.tablename OFF
With "SET IDENTITY_INSERT" you can only insert values and cannot update values…
See Hilary’s bloghttp://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/ andhttp://mssqltips.com/tip.asp?tip=1274 for not setting for replication. Fyi, do not attempt to open the table in Enterprise Manager as it tends to timeout definetly, best bet is query analyzer. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks Satya. However we are not replicating identity column. Our identity column is only on destination table (Subscriber). Currently we dont have issues with replication. My question was "I know we can insert values in "identity" column but how can we update values in identity column???" Since this process hangs up in Enterprise manager, how can we update identity column or recreate identity column in existing table. How to do this in query analyzer???
Hi GROUP, Any update on above issue, We need this urgently done. Thanks
quote:Originally posted by viksar Hi GROUP, Any update on above issue, We need this urgently done. Thanks
You cant update identity column
Why do you want to do? Madhivanan Failing to plan is Planning to fail
See sp_scriptdynamicupdproc from MASTER database and try whether you can tweak a bit. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>