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?

Backtrack

Running Profiler, I see that MS creates a temporary table, copies the data across, then drops the existing table and renames the temp table. Probably because there can't be more than one identity column. – Backtrack

Answer 1

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."

Juvenile

Answer 2

MSSQL does not allow you to add or alter an Identity on an existing column via TSQL very easily. You would have to drop the column and re-add it. Needless to say this can play hell with FK relations. You can do it directly in the enterprise manager. However that won't be fun if you have to do this to a LOT of columns.

Is it necessary to create a new column, move the values across, drop the original column and rename the new?

Yup, and don't forget to fix/update all indexes, foreign key relationships, etc. that are tied to that column

Garling

ANd don't even think of doing this to a large table! – Kleper

I'd generally recommend against editing any production table in the Enterprise Manager. Sometimes it will copy, drop, and paste your table to accomplish the desired action. – Garling

Answer 3

You can use DBCC CHECKIDENT('tablename', RESEED, seedvalue)

example: DBCC CHECKIDENT('Customers',RESEED, 1350) run DBCC CHECKIDENT('Customers') again to check if current seed value was set.

However as mentioned in previous answers this will not change existing values stored in the identity column. It will only change seed value so the next row that is inserted will start with that value. Identity increment remains same (not changed) and can not be changed with DBCC.

Horst

Just restates the problem, doesn't provide an answer. – Backtrack

Answer 4

"Is it necessary to create a new column, move the values across, drop the original column and rename the new?"

Actually in Enterprise Manager, when you add an ID column to an existing table (or change an INT PK field to an INT PK ID), it does this behind the scene.

Vocoid