|
Category: SQL Server
view more software Tips and Tricks
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 |