reset-identity-in-sql-server

Reset Identity in SQL Server

2011-06-20

We have already had a post about "Clear ID counter after delete data table" which is just for reset identity column in SQL Server using Truncate command.

However, Truncate is just the one method of resetting Identity in SQL Server, and it is only for the table which no foreign keys defined. If you use Truncate for the table with foreign keys defined, you will get the following error message:

"Cannot truncate table 'TableWithForeignKey' because it is being referenced by a FOREIGN KEY constraint."

In the case of table with foreign key, you have to use DBCC CHECKIDENT command:

DBCC CHECKIDENT 
 ( 
    table_name
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

Arguments Descriptions:

table_name

Is the name of the table for which to check the current identity value. The table specified must contain an identity column. Table names must comply with the rules for identifiers.

NORESEED

Specifies that the current identity value should not be changed.

RESEED

Specifies that the current identity value should be changed.

new_reseed_value

Is the new value to use as the current value of the identity column.

WITH NO_INFOMSGS

Suppresses all informational messages.

For example, if you want to reset the identity from the seed 1 for table FEEDBACK_LOG in a database named MyDB, you can use the following SQL script:


DBCC CHECKIDENT('[MyDB].[dbo].[FEEDBACK_LOG]',RESEED, 1)