• ARTICLES
SEARCH

How-To Geek

Reset Identity Column Value in SQL Server

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.

It would be wise to first check what the current identify value is. We can use this command to do so:

DBCC CHECKIDENT (‘tablename’, NORESEED)

For instance, if I wanted to check the next ID value of my orders table, I could use this command:

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, I can use this command:

DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.

Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)

DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999)

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 03/7/07

Comments (43)

  1. Rukowen

    wonderful..
    That’s all I need, Thanks in advanced!…
    Have a nice weekend..

  2. Himani

    Hey,,
    thus is what inwas looking for..
    gee thankss

  3. kevin

    thanks!

  4. Ste

    Mitico!

  5. Dominic Herard

    Thanks a whole lot!

  6. Nick

    great concise page. thanks. way easier than ms help files.

  7. lakshmi

    This is great. But how to make the following code work:
    declare @seqno int
    set @seqno = DBCC CHECKIDENT (orders, NORESEED)
    DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, @seqno)

  8. tdow

    I created a script that will do what you want, lakshmi. Just replace MyId and mytbl below:

    DECLARE @MaxId INT

    SELECT @MaxId = MAX( MyId )
    FROM mytbl (NOLOCK)

    PRINT ‘Resetting Identity value to : ‘ + CONVERT( VARCHAR, @MaxId )
    PRINT ”

    DBCC CHECKIDENT (‘mytbl’, RESEED, @MaxId)

    PRINT ”

    – Verify
    DBCC CHECKIDENT (‘mytbl’, NORESEED)

  9. Jeff

    Thanks Geek – worked like a charm

  10. Amit Yenurkar

    Thanks a lot!!!!!!!!!
    Useful Stuff…………

  11. Nirman Doshi

    Thanks a lot
    i was searching for this

  12. haridsv

    Thanks a lot. For me, if I use the value of 0 to reseed, sql server 2005 didn’t start from 1, but actually from 0. May be the +1 statement is not correct?

  13. Prodis

    Thank you for information. It was usefull.

  14. Yogesh

    Thanks :-)

  15. Ric

    Brilliant, thanks

  16. Mick

    Great, thanks. Strange thing though. It worked exactly as advertised testing in SQL Server Management Studio Express with SQL Server 2005. But when I coded it up in Java (using sqljdbc) running against the same database, the next value was not reseed + 1, but the reseed value itself (as haridsv found).

  17. A Hot Redhead

    Thank you, The Geek!

  18. Andrea

    Thank you!

  19. Enrique Pessoa

    Dear Haridsv and Mick,

    Reseeding does not work correctly when the identity was not used yet.

    If you want reseed to work from the moment that the table was created, you can, for instance, insert something just after creating the table.

    create table mytbl (id_column int NOT NULL IDENTITY (1, 1), char_column char(1))
    insert into mytbl (char_column) values (‘a’)
    delete from mytbl

    After that you can DBCC CHECKIDENT(‘mytbl’,RESEED,0) as much as you wish.

    Regards,
    Enrique

  20. Saeed

    Thats all we need thank you veary much

  21. Waleed

    A very helpfull post.. many thanks.

  22. Atower

    3 years later your post keeps being useful, TY.

  23. Alcontreras

    If you have the SQL Server Management Studio then you can simply open the table in design mode, select the field, go to the Identity Seed property (in the Identity Specification group) and set it to the velue you need.

  24. Ron

    Thanks for the useful info!

  25. Saurabh

    Thanks for help. It help me a lot.

  26. Venkatx5

    Dear Haridsv and Mick,

    I also got the same results as it starts with 0. Here the thing which makes difference is clearing the tables. If you use TRUNCATE to clear your tables then “0″ will be the initial value. If you use DELETE then it’ll start from “1″.

    Hope it clarifies..

    Regards,
    Venkatesh R

  27. afshin

    tanks a lot my friends.

  28. Shyam

    Very Useful

  29. Jemson Sentillas

    Thanks a lot. Very useful indeed!

  30. Durga

    Very very good!.. Thanks alot. Please keep on post some more interesting topics.

  31. jorge

    Well, three years later your post has saved me a lot of time
    Thanks!

  32. Adnan Džaferović

    Thanks!

  33. Simran

    The above mentioned code is to get the current id. But I want something that automatically takes next id depending upon the condition

  34. Joachim

    You can also use a conditional RESEED to avoid the 0 values in new tables.

    IF IDENT_CURRENT(‘MyTable’)>1 DBCC CHECKIDENT (‘MyDB.dbo.MyTable’, RESEED, 0)

  35. Mahdi

    Helpful
    Thanks!

  36. soujanya

    Thanks a lot..was very helpful

  37. Tickles

    thanks :)

  38. Solomarc

    DBCC CHECKIDENT(tablename, RESEED) will set the seed to the existing seed value or the maximum value in the column+1 whichever is greater so if you just want to reseed to the curent max value in your identity field use this:

    DBCC CHECKIDENT(tablename, RESEED, 0)
    DBCC CHECKIDENT(tablename, RESEED)

  39. Arthur

    Very good! Was very helpful!

  40. FD

    very informative THX

  41. ahmad

    very thanks

  42. Abdollah

    Thank for all your helps

  43. Roxo

    Hey hero, thank you !!

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!