Reset table identity value in SQL Server with DBCC RESEED

In a table with 4 rows and one identity column (in this example it is the column "ID"), 4 is the last identity value:

If we delete all 4 records, and insert one new record, the identity value increases by one, and the table looks like this:

To reset the identity value, run the following command:
DBCC CHECKIDENT('Table_name', RESEED, 0)

After deleting all records from the table, and inserting one record, the identity value is '1' again:

If we run the identity reset command again:
DBCC CHECKIDENT('Table_name', RESEED, 0)
and try to insert one row to the table, and if a PRIMARY KEY or UNIQUE constraint exists on the identity column, it will violate the uniqueness constraint, because it will try to insert value '1' into identity column again:

If we run the following command:
DBCC CHECKIDENT('Table_name', RESEED, 100)
and insert one row into the table, the next identity value will be 101:

http://msdn.microsoft.com/en-us/library/ms176057%28v=sql.105%29.aspx
"Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value."

To see how to insert values into IDENTITY column in SQL Server, read related article:
How to insert values into IDENTITY column in SQL Server

If the post helped you, please share it:
Pin It

1 comment to Reset table identity value in SQL Server with DBCC RESEED

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">