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
Hrmmm.. Why not use Truncate statement?
e.g.
truncate table tblname
you can use:
truncate table tbl_name
same result
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 (-;
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.
Yep. For this reason TRUNCATE TABLE is practically useless
It's a very good article. It helped me very much.
Thank you.
– 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)
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.
Thanks,
How to reset the identity column value to 0 for temporary table variable in sql server 2005?
It's a very good article.
Thanks for this article