Updating an identity column

Rated 4.87/5 based on 863 customer reviews

There is not a stored procedure like there is for SQL Server 2005 and later.

The following code snippet will allow you to change this value across the board for all tables in a database as well, but this is updating the system table syscolumns directly.

updating an identity column-12

updating an identity column-47

updating an identity column-88

To retain existing values create a new table and insert using identity_insert.

Although it is great that SQL Server handles the not for replication setting for you, what is the process to turn this off or turn this on for tables?

The "Not For Replication" setting for identity columns allows replication to pass the ID value from the publisher to the subscriber without the subscriber creating a new ID.

But if you remove replication this setting stays as yes or if you need to keep a development schema (which is not replicated) in synch with your production schema (which is replicated) how can you do this without having to manually change each table via the table designer?

In SQL Server 2008 a new option for the ALTER TABLE command allows you to turn on and off the "Not For Replication" setting.

Leave a Reply