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:

Similar Articles	Tinyhacker - Tiny Geek Hacks
Command Line Hack for: "Terminal Server Has Exceeded the Maximum Number of Allowed Connections" Keyboard Ninja: Insert Tables in Word 2007 Easier Table Column Selection in Microsoft Word How to Send and Receive Hotmail from Thunderbird See Task Type at a Glance by Adding the Icon Column to the Outlook 2007 To-Do Bar	Jazz Up MMS on the iPhone using FunMail Free Falling Politicians, Your Choice Of The Aisle Find What You Are Looking for Fast using Windows Grep Gizmodo’s Comprehensive List Of Black Friday Bargains ESET Smart Security 4 – Good Security for Everyone Turn Your PC into a Streaming Music Server using Sockso
Latest Software Reviews	Super User Daily
PCmover Professional Spyware Doctor + Antivirus 2010 Cakewalk Guitar Tracks Pro USB BitDefender Total Security 2010	Can Photoshop extract similar content from two dissimilar images? How to make RivaTuner load OC settings from the startup folder TightVNC problem connecting to iPod How to get a copy of Visual Studio 2003?
Geek Arcade	Popular Forum Threads
Open Doors Boombot Demolition City Heavy Weapons	Reaching the network printer on XP *from* Win 7 Making a simple animation building new computer, please leave feedback PLEASE HELP MY HARDRIVE SHRANK!!!!

Comments (16)

1. March 23, 2007 10:04 pm
   Rukowen

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

2. April 23, 2007 11:48 pm
   Himani

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

3. May 7, 2007 10:02 pm
   kevin

   thanks!

4. June 7, 2007 10:12 am
   Ste

   Mitico!

5. July 28, 2007 4:21 pm
   Dominic Herard

   Thanks a whole lot!

6. February 8, 2008 10:10 am
   Nick

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

7. March 12, 2008 4:13 am
   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. April 15, 2008 5:43 pm
   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. October 8, 2008 11:39 am
   Jeff

   Thanks Geek – worked like a charm

10. April 6, 2009 7:14 am
    Amit Yenurkar

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

11. July 2, 2009 6:55 am
    Nirman Doshi

    Thanks a lot
    i was searching for this

12. July 22, 2009 7:39 pm
    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. August 4, 2009 9:03 am
    Prodis

    Thank you for information. It was usefull.

14. September 4, 2009 11:05 pm
    Yogesh

    Thanks

15. September 18, 2009 5:21 am
    Ric

    Brilliant, thanks

16. November 18, 2009 11:49 am
    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).