How To Reset Identity Seed In SQL Server?

Often while testing the applications during the development phase you may need to insert data to the database tables, delete them and re-insert them multiple times. Sometimes you may need to reset the auto incremented identity column to start again from the beginning or from a specific number again. Here, we will see how to reset identity seed in SQL Server.

To re-seed the identity column, you can use use the DBCC CHECKIDENT management comment. Using CHECKIDENT, you can specify a new identify value and re-seed. You can also checking the current identify value and decide your re-seeding accordingly.

Checking Current Identity Value

Before re-seeding, it is a good practice to check the current and the maximum identity value. He is how do check it:

Syntax

DBCC CHECKIDENT ( <table>, NORESEED )

Where:
<table> = Name of the table which needs to be checked.

Returns:
A message with the value of current identity and current column.

Example

In this example you can see the current identity value is higher than the current column value.

DBCC CHECKIDENT( [MTB_Table_E], NORESEED)

/* Result */
Checking identity information: current identity value '98', current column value '50'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Check Current Identity Value Using DBCC CHECKIDENT

Reset Identity Seed

Now, we will see how to reseed the identity using CHECKIDENT.

Syntax

DBCC CHECKIDENT ( <table>, RESEED, <new-seed> )

Where:
<table> = Name of the table which needs to be checked.
<new-seed> = New re-seed value for the identity column.

Returns:
A message or exception

Example

DBCC CHECKIDENT( [MTB_Table_E], NORESEED)

DBCC CHECKIDENT( [MTB_Table_E], RESEED, 50)

DBCC CHECKIDENT( [MTB_Table_E], NORESEED)

/* Result */
Checking identity information: current identity value '98', current column value '50'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Checking identity information: current identity value '98'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Checking identity information: current identity value '50', current column value '50'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Reset Identity Seed In SQL Server

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.