Reset autonumber (IDENTITY) column in SQL Server

Sometimes we need to reset the autonumber - identity column in a table.

Using the DELETE statement is not enough. The statement only deletes the data but not reset the identity column.

So how can we do it? First, wen need to delete the data from the table and after execute the DBCC command with CHECKIDENT switch in order to reset the data:

   1:  DELETE FROM tblName

   2:  DBCC CHECKIDENT (tblName,RESEED, 0)

(tblName is the table name).

Just be careful before deleting your data...

Technorati Tags: TSQL

Published 08 October 2007 09:28 PM by Maor David-Pur

תגים:TSQL

Comments

# Aviv said on 08 October, 2007 09:45 PM

Hrmmm.. Why not use Truncate statement?

e.g.

truncate table tblname

# EvyatarShalom said on 09 October, 2007 06:38 AM

you can use:

truncate table tbl_name

same result :P

# Amir said on 10 October, 2007 12:10 AM

If you are already willing to delete all the data in your table, "TRUNCATE TABLE tblName" will have the same result in a single statement...

Just a simple TSQL (-;

# Maor David-Pur said on 10 October, 2007 12:45 AM

Thanks for your comments.

You all right guys, but there is little reservation:

You can use TRUNCATE TABLE only if there are no contrains and foreign keys related with this table. Usually there are.

# Tony said on 22 October, 2007 11:57 PM

Yep. For this reason TRUNCATE TABLE is practically useless

# Maor David said on 02 November, 2007 02:01 PM

Well, October was an interesting month. I wrote 30 posts, and these are the 5 posts you mostly interested

# Saibaba NVV said on 27 November, 2007 06:49 PM

It's a very good article. It helped me very much.

Thank you.

# Laine said on 05 December, 2007 09:19 PM

-- To reset to the highest available ID value

declare @max_id bigint

select @max_id = max(id_col_name) from table_name

DBCC CHECKIDENT(table_name, RESEED, @max_id)

# Adrian Lewis said on 14 December, 2007 03:11 PM

There is another strange piece of behaviour associated with this. if you DELETE FROM a table, and then RESEED with a reseed value of 1, then the first IDENTITY value after an INSERT will be 2. But if you TRUNCATE a Table and RESEED with a value of 1, the first IDENTITY value will be 1! In order to start at 1 after a DELETE, you have to RESEED to 0, as you have done in your example. If you do this after a TRUNCATE, then the first Identity value inserted will be 0!

Can anyone proveide a rational explanation of why this hould be so? It happens in both SQL 2000 and 2005.

_Ade.

# Prakash said on 25 January, 2008 09:25 AM

Thanks,

How to reset the identity column value to 0 for temporary table variable in sql server 2005?

# Maor David said on 17 May, 2008 04:53 PM

Last week it was exactly one year since I started blogging, so this is my blog first birthday!! Come and read about the blog's statistics, top posts and more.

# Maor David said on 17 May, 2008 04:54 PM

Last week it was exactly one year since I started blogging, so this is my blog first birthday!! Come and read about the blog's statistics, top posts and more.

# Debasish Samal said on 30 June, 2008 09:34 AM