Home / Software / Tips and Tricks / DELETE, TRUNCATE and RESEED Identity column in SQL Server

DELETE, TRUNCATE and RESEED Identity column in SQL Server

Updated:  01/09/2013 12:01 PM
Author:  Shiju Mathews

Status:    Resolved.


Create a test table with an Identity column and a Test vaule field.



Insert 3 rows into the table


Results:
IDENTITYNo
-----------
0
1
2
(3 row(s) affected)

Use DELETE statement


Results
(3 row(s) affected)
(1 row(s) affected)
IDENTITYNo
-----------
3
(1 row(s) affected)


When the DELETE statement is executed, it will delete all the rows. However, when a new record is inserted the identity value is increased from 2 to 3. The Identity value does not reset but keep on increasing.

Use of TRUNCATE statement



Results:
(1 row(s) affected)
IDENTITYNo
-----------
0

(1 row(s) affected)

When the TRUNCATE statement is executed it will delete all the rows. However, when a new record is inserted the identity value 0 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.

Use of RESEED statement



Results:

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

(1 row(s) affected)
IDENTITYNo
-----------
0
1

(2 row(s) affected)

The seed value will be set to 0, and the identity value for the next row will be 0 + 1 = 1