Subscribe to 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)

| More
This article was originally written on 03/7/07 Tagged with: Database, SQL Server

Daily Email Updates

You can get our how-to articles in your inbox each day for free. Just enter your name and email below:


Name:
Email:

Comments (16)

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


Leave a Comment




Leave your friendly comment here.

If you have a computer help question, click here to leave it on the forums instead.

Note: Your comment may not show up immediately on the site.

Our Friends
Getting Started


About How-To Geek
What Is That Process?
svchost.exe
jusched.exe
dwm.exe
ctfmon.exe
wmpnetwk.exe
mDNSResponder.exe
wmpnscfg.exe
rundll32.exe
wfcrun32.exe
Ipoint.exe
Itype.exe
Wfica32.exe
Mobsync.exe
conhost.exe
Dpupdchk.exe Adobe_Updater.exe

Copyright © 2006-2009 HowToGeek.com. All Rights Reserved.