An Identity column in SQL Server is used to auto number the rows. See this great post on Autonumbering & Identity Columns. When you delete rows from the table, the identity will not reset but you have few options of doing it yourself. The SQL Server Management Studio GUI doesn’t support this option…

1. DBCC CHECKIDENT

This is the most easy way, just set it yourself to the desired value. be careful not to set the value to lower than any other value in the table. you might get a primary key violation. Here is how to do it:

DBCC CHECKIDENT ( <table name>,RESEED,<new value>)

2. Drop and Add the column

This might be more safe, because the new seed value is assigned automatically, but it requires more work and also changes the order of the columns! If you access columns by index in your code this is destructive – remember to reorder them back the way they were… Here is how to do it assuming the field also have a primary key set:

ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> DROP COLUMN <column name>
ALTER TABLE <table name> ADD <column name> bigint identity (1,1) NOT NULL
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY CLUSTERED(
<column name> ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

3. Drop and Create the table

This will solve both problems (setting a safe new value while keeping columns order) but it also requires more work – no free lunch…

Create a new temporary table with the same layout, copy all data into the temporary table, drop the old table and rename the temporary table.

You can choose whatever meet your needs, depends how much you want to work for it…

Tags :

17 Responses to “How to reset Identity Increment value in MS SQL”


  1. Roboblob

    Said on March 29, 2008 :

    Very useful tips.

    Thumbs Up!

  2. Dev

    Said on March 30, 2008 :

    Why wouldn’t you just use TRUNCATE TABLE?

  3. Michael

    Said on March 30, 2008 :

    Dev-
    1. There is no TRUNCATE command in the SQL standard.
    2. The TRUNCATE command is similar to DELETE but is more efficient for a
    large data tables, since it does not scans the table
    3. By suggesting of using TRUNCATE you missed the point of the article, the effect of
    this operation is different.
    4. I guess that DROP SEQUENCE is more appropriate.

  4. Tony Rogerson

    Said on March 30, 2008 :

    Good post; though if you want to start from an empty table that has had rows in it you can use TRUNCATE TABLE so long as there are no foreign keys.

    Not sure what Michael is referring to, IDENTITY isn’t in the standard either so if you are already using non standard syntax then what’s the problem using TRUNCATE TABLE?

  5. Shahar A

    Said on March 30, 2008 :

    I Agree that TRUNCATE TABLE is a good way, but it works only in case you delete all the data in the table. Sometimes you delete just part of the rows and reset the identity accordingly

  6. Michael

    Said on March 30, 2008 :

    1. TRUNCATE TABLE is inapropriate since we are not interested in deleting data.
    2. I suggested to use sql standard means for example
    ALTER SEQUENCE serial RESTART WITH 105;

    IMHO it shall work. (Last time i wrote sql was on Oracle and on Postgresql, so i am sorry if my answer was not MS SQL specific)

  7. em

    Said on April 17, 2008 :

    The #1 option resets the Identity Seed value, not the Increment value

  8. Will

    Said on April 22, 2008 :

    There is actually an easier way that I just found out using SQL server management studio. If you click Modify on the table list on the table you want and once that opens in the main pane right click on the table in the list and delete it. Go back to the main pane again and check/uncheck for allow nulls (or do something to “make a change”) and save it.

    The structure is all the same and the index is reset.

  9. The Dave

    Said on October 1, 2008 :

    Your title is actually misleading.. your not changing the increment value, your changing the seed, big difference between the two. The increment value is what determines what the next value will be. If your seed is 1 and your increment is two.. Your identity field would start at 1, but the next entry would be 3, 5, 7 etc.

  10. Tayfun

    Said on January 26, 2009 :

    Nice post. DBCC CHECKIDENT ( ,RESEED,) works.

  11. Ed

    Said on April 26, 2009 :

    How do you reset the AUTO INCREMENT of a table in Visual Web Developer!? This damn thing doesn’t support ALTER TABLE, DBCC, or TRUNCATE table. How the hell do you do it in there? This is pissing me off!

  12. JONATHAS

    Said on June 30, 2009 :

    IS VERY SIMPLE, USE THE COMMANDS ABOVE

    SP_CONFIGURE ‘ALLOW UPDATES’, 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    –THEN COMMANDS.. EX. UPDATE INDEX TO 0
    UPDATE SYSINDEXES SET ROWCNT = 0 WHERE ID IN (SELECT ID FROM SYSOBJECTS WHERE NAME = ‘”TABLENAME”‘)

    GO
    SP_CONFIGURE ‘ALLOW UPDATES’, 0
    GO
    RECONFIGURE WITH OVERRIDE
    GO

  13. Roland

    Said on December 2, 2011 :

    I tried to use it in a storedprocedure there the
    UPDATE SYSINDEXES….causes the following error:

    259: Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

    Is there a way to overcome this?

  14. nobody

    Said on September 18, 2012 :

    Very useful, exactly what I was looking for…

3 Trackback(s)

  1. Mar 29, 2008: DotNetKicks.com
  2. Mar 31, 2008: dvilchez.net » Blog Archive » Sql tips&tricks
  3. Apr 17, 2009: Dev102.com March 2009 Roundup | Dev102.com

Post a Comment