Changing Identity Seed in SQL Server (Permanently!)

Question

Is there any way of changing the identity seed for an identity column permanently? Using DBCC CHECKIDENT just seems to set the last_value. If the table is truncated all values are reset. ``` dbcc checkident ('__Test_SeedIdent', reseed, 1000) select name, seed_value, increment_value, last_value from sys.identity_columns where [object_id] = OBJECT_ID('__Test_SeedIdent'); ``` returns ``` name seed_value increment_value last_value ------------------------------------------------- idIdent 1 1 1000 ``` I was hoping that some syntax like ``` alter table dbo.__Test_SeedIdent alter column idIdent [int] identity(1000,1) NOT NULL ``` would exist. Is it necessary to create a new column, move the values across, drop the original column and rename the new?

Accepted Answer

From Books Online:

"To change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. The order in which the rows are updated is not guaranteed."

Changing Identity Seed in SQL Server (Permanently!)

Solution

Problem