How to Reset an Identity Seed in Microsoft SQL

Microsoft SQL Server supports the use of one IDENTITY column per table. An IDENTITY column provides a numeric value which can be used as a key or reference value. IDENTITY columns are automatically incremented each time a new row is added to the table. The IDENTITY value begins at a user-provided value, called the seed, or at 1 if no value is specified, and increases by the user-supplied amount, called the increment, or by 1 if no value is specified. Resetting the starting value or increment of IDENTITY column requires either dropping the column and creating a new one or truncating the table and running DBCC CHECKIDENT .

Drop and recreate a column

• 1

  Open a connection to the desired database using Microsoft SQL Server Management Studio or your preferred application.

• 2

  Type a query to drop the existing identity column. This must be done before creating a new identity column because each table can have only one identity column. The syntax is:

  ALTER TABLE [table_name] DROP COLUMN [column_name]

  This will completely remove the current IDENTITY column from the table, causing the loss of existing IDENTITY values. If these values will be needed, they should be backed up or copied to a non-identity row first.

• 3

  Type and execute a query to add a new identity column. The syntax is:

  ALTER TABLE [table_name] ADD COLUMN [column_name] IDENTITY ([seed],[increment])

  Supply your desired starting point for the seed and the increment value for the increment. This will create a new column with no skipped data values.

Using Truncation and DBCC CHECKIDENT

• 1

  Open a connection to the desired database using Microsoft SQL Server Management Studio or your preferred application.

• 2

  Truncate the table. This removes all data from the table, so be sure there is no data you wish to keep. The syntax is:

  TRUNCATE [table_name]

• 3

  Run DBCC CHECKIDENT to modify the seed value. This does not change the increment value. Increment values can only be changed using the method in Section 1. The syntax for this is:

  DBCC CHECKIDENT([table_name], RESEED, [new_seed])

  The new seed value will be the first value used when inserting a new row.