Web of Ideas

Developers blog

 

How to reset an identity column seed in SQL server

06 Dec 2010, 09:31   Subscribe to Richies blog (RSS)

Always have to look this up so though it worthy of a blog post. An identity column has an automatically generated value that increments for each row added to the table. The starting (‘seed’) value and increment value can be specified, but are usually both set to 1.

To set the identity seed back to 1 for a table:
DBCC CHECKIDENT('[table name]', RESEED, 1)

After running this SQL, the next row to be added will get a value of 1 in the identity column.

Alternatively, to delete all rows on the table and reset the identity, do a table truncate. A truncate also resets the identity seed to its starting value:
TRUNCATE TABLE [table name]

A few gotchyas on identity columns:

  • There is nothing to guarantee that the value in an identity column will be unique or continuous.
    • If the seed is reset to a lower value without deleting rows, duplicate values will get created. SQL Server does not prevent this.
    • If rows are deleted (without resetting the seed), gaps will appear in the identity values. Again, SQL server does not prevent this.
  • Only one identity column is allowed per table (which is why the SQL above relates to a table not a column).
  • A key column can be an identity column. In fact this is a common technique to generate unique keys (bearing in mind the uniqueness point above).



Comments6
Ratings1584
Average rating100%  100%
Posted06 Dec 2010, 09:31

View blog

Richies blog


Next blog entry: Microsoft Access as a website database

Rate this blog entry

1 (Very poor)  2 (Poor)  3 (Satisfactory)  4 (Good)  5 (Very good)   Enter your name   


Comments


No comments have been made.

Post a comment



Please enter your name to be displayed against the comment