Managing Identity Values

When using Replication, each database server needs to be assigned a unique range for "next value" of the PrimaryKey field. Managing those ranges is outside the scope of this article, but consider the non-production instances of the replicated database. They are not included in replication merges, and are not managed by the "Identity Manager" process. The developer therefore needs to manually update both the NextValue and the CheckConstraint for these tables in order to reliably INSERT a record during a unit test in the non-production database instance.

Here are some handy examples of commands for viewing and changing Identity values:

SELECT IDENT_CURRENT('MyTable') AS LastIdentityValue
Shows the most recently used IDENTITY value for MyTable.

SELECT IDENT_SEED('MyTable') AS IdentitySeed
Shows the increment value for the IDENTITY column in MyTable.

DBCC CHECKIDENT('MyTable', RESEED, 56752)
Sets the most recently used IDENTITY value for MyTable to 56752.