Change value of existing rows of an identity column

[Harini N K]

Hi experts, I want to change value of an identity column without dropping table / references / constraints / deleting rows from that table. I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging. Is there any SQL statement to modify that column as Identity Seed to NO.?

Harini

[Mike Dimmick]

Harini N K wrote:

I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging.

This normally indicates that there are existing connections open that have taken Schema Stability locks on the table. These locks should be shortlived - check for transactions that haven't been committed or rolled back. To disable the identity feature completely, use ALTER TABLE _table_ ALTER COLUMN _column_name data_type_. It sounds, though, as if you want to set the identity value of some existing rows, or set the identity value of new rows you're inserting. To do this, use SET IDENTITY_INSERT _table_ ON. It's normally a good idea to turn this off when you're done. Also, use DBCC CHECKIDENT to ensure that the next row inserted which uses the identity value does not get a clashing value.

Stability. What an interesting concept. -- Chris Maunder