Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

How to reset an identity value after a table has been emptied

From Wiki

Jump to: navigation, search

If you have a table with an identity column on it and if you delete all the data the identity is not reset. There are two ways to accomplish this: 1) You truncate the table 2) if you can't truncate then you can delete all the data followed by a DBCC CHECKIDENT command


Let's take a look, first create these two tables

  1. create table TestID2 (id int identity not null, id2 int)
  2.  
  3. create table TestID3 (id int identity not null, id2 int)

Now insert one row in both tables

  1. insert into  TestID2 values (1)
  2.  
  3. insert into  TestID3 values (1)

Truncate one table and use delete for the other

  1. truncate table TestID2
  2.  
  3. delete TestID3

Now insert one row into both tables again

  1. insert into  TestID2 values (1)
  2.  
  3. insert into  TestID3 values (1)

Now run a select query against both tables

  1. select * from TestID2

Output

id          id2
- - - - -   - - - - - - 
1           1


  1. select * from TestID3

Output

id          id2
- - - - -   - - - - - - 
2           1


As you can see the value of the id column is 2 in the table that we have used delete on. Let's repeat the same process from before, but now we will also run the DBCC CHECKIDENT command

  1. truncate table TestID2
  2.  
  3. delete TestID3

Before we run DBCC CHECKIDENT , take a look at what Books On Line has to say

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since it was created, 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.


So in other words if we want it to start at 1, we need to reseed with a 0 as the reseed value. Now run this command

  1. DBCC CHECKIDENT ('TestID3', RESEED, 0);

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

Now insert a row again

  1. insert into  TestID3 values (1)


  1. select * from TestID3

Output

id          id2
- - - - -   - - - - - - 
1           1


As you can see, the value is now 1 for that row


Contributed by: --SQLDenis 17:33, 29 April 2010 (GMT)


Part of SQL Server Programming Hacks

Section Handy tricks

753 Rating: 2.3/5 (66 votes cast)