Reset autonumber (IDENTITY) column in SQL Server

8 באוקטובר 2007

תגיות:
10 תגובות

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:
הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

10 תגובות

  1. Aviv8 באוקטובר 2007 ב 21:45

    Hrmmm.. Why not use Truncate statement?
    e.g.
    truncate table tblname

    להגיב
  2. EvyatarShalom9 באוקטובר 2007 ב 6:38

    you can use:

    truncate table tbl_name

    same result :P

    להגיב
  3. Amir10 באוקטובר 2007 ב 0:10

    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 (-;

    להגיב
  4. Maor David10 באוקטובר 2007 ב 0:45

    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.

    להגיב
  5. Tony22 באוקטובר 2007 ב 23:57

    Yep. For this reason TRUNCATE TABLE is practically useless

    להגיב
  6. Saibaba NVV27 בנובמבר 2007 ב 18:49

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

    Thank you.

    להגיב
  7. Laine5 בדצמבר 2007 ב 21:19

    – 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)

    להגיב
  8. Adrian Lewis14 בדצמבר 2007 ב 15:11

    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.

    להגיב
  9. Prakash25 בינואר 2008 ב 9:25

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

    להגיב
  10. Debasish Samal30 ביוני 2008 ב 9:34

    It's a very good article.
    Thanks for this article

    להגיב