Reseed Identity Column in SQL Server 2008 R2

I want to share some important points while reseeding in SQL Server 2008 R2
In order to reseed a table, the following query can be used:
DBCC CHECKIDENT (tableName, reseed, 0)
But there is a minor issue that we must to consider.If we TRUNCATE table instead of DELETE, the above query will show the following message:
Checking identity information: current identity value 'NULL', current column value '0'.
This means you cannot use reseeding for newly truncated table. In such a case,if you want to set starting value:
1)Open table in desing mode
2)Select identity column
3)Expand Identity Spesification
4)Set Identity Seed


You can just insert a dummy record and DELETE it.After that you can run script above.

It is important to remember that, the value that you set while reseeding(whether using query or designer) is not the inital value. Identity increment will be added to this value when insterting your first record.
(initial value + identity increment)

No comments:

Post a Comment