When you use identity columns in SQL server you may have recognized that when deleting all table data your identity value stays at its latest position. Sometimes this is useful, sometimes it is not. Then you need to set this value to your preferred starting point. Another scenario would be that you need to start with a special position in a new table for example to meet naming conventions.
All you need is the DBCC CHECKIDENT command.
First you may want to know the value your identity column has at the moment:
DBCC CHECKIDENT ('tablename', NORESEED)
If you want to set the identity value to any other value use this command:
DBCC CHECKIDENT ('tablename', RESEED, 0) -- next value is 1
DBCC CHECKIDENT ('tablename', RESEED, 9) -- next value is 10
DBCC CHECKIDENT ('tablename', RESEED, 99) -- next value is 100 and so on
To use this command you must own the table, or be a member of the sysadmin, db_owner or db_ddladmin fixed database role.
Recent Comments