SQL 2005+

Resetting SQL Server Identity Columns

by Richard Carr, published at http://www.blackwasp.co.uk/ResetSqlServerIdentity.aspx

Microsoft SQL Server identity columns provide a useful way to automatically generate consecutive numeric values for identifying rows. During software testing, IDs may be generated and then deleted. Afterwards, you may want to reset the number sequence.

Database Console Commands

Microsoft SQL Server allows maintenance tasks to be performed using Database Console Commands. These commands are executed against a database using the DBCC keyword followed by the name of the command and any parameters that are required.

CheckIdent Command

In order to reset an identity column, the CheckIdent database console command is used. This command can be used to determine or modify the identity seed for a table. The seed can be thought of as the value of the last identity entered for sequential identity columns. If the identity seed is currently twenty and is incremented by one for each row inserted into a table, the next row will automatically have an identity of twenty-one.

Retrieving the Identity Seed

To determine the current identity seed for a table, the "DBCC CheckIdent" command is executed with a single parameter. The parameter specifies the name of the table to be checked. For example, to determine the identity seed for a table named "Employees", the following command can be used:

DBCC checkident ('Employees')

/* OUTPUT

Checking identity information:
  current identity value '4', current column value '4'.
  
*/

In the above example, the results confirm that the identity seed value is currently four.

Repairing the Identity Seed

Occasionally a problem with a database can lead to the identity seed being incorrectly set. If, for example, the identity seed is incorrectly reset to zero but there are rows in the table, there is the risk of creating duplicate identities. If this problem occurs, executing the DBCC command with an additional "reseed" parameter can rectify it. The identity seed will be changed to be the same value as the highest identity value in the identity column.

DBCC checkident ('Employees', reseed)

NB: Using this command after simply deleting some rows will not reset the identity seed.

Changing the Identity Seed

Sometimes you will want to change the identity seed manually. This can be to reset the value after deleting rows or, perhaps following an upgrade, to start a new, higher range of values. To achieve this, a third parameter is added to the DBCC command specifying the new seed. To reset the seed for the "Employees" table to one thousand, the following command is used. The next row would then have an identity of one thousand and one.

DBCC checkident ('Employees', reseed, 1000)

NB: Care must be taken that the identity is not set to a value that could cause later duplication of IDs.