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)
Got Feedback? Join the discussion at discuss.howtogeek.com
Comments (43)
Programmer by day, geek by night, The Geek, also known as Lowell Heddings, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on Google+ if you'd like.
- Published 03/7/07
wonderful..
That’s all I need, Thanks in advanced!…
Have a nice weekend..
Hey,,
thus is what inwas looking for..
gee thankss
thanks!
Mitico!
Thanks a whole lot!
great concise page. thanks. way easier than ms help files.
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)
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)
Thanks Geek – worked like a charm
Thanks a lot!!!!!!!!!
Useful Stuff…………
Thanks a lot
i was searching for this
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?
Thank you for information. It was usefull.
Thanks :-)
Brilliant, thanks
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).
Thank you, The Geek!
Thank you!
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
Thats all we need thank you veary much
A very helpfull post.. many thanks.
3 years later your post keeps being useful, TY.
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.
Thanks for the useful info!
Thanks for help. It help me a lot.
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
tanks a lot my friends.
Very Useful
Thanks a lot. Very useful indeed!
Very very good!.. Thanks alot. Please keep on post some more interesting topics.
Well, three years later your post has saved me a lot of time
Thanks!
Thanks!
The above mentioned code is to get the current id. But I want something that automatically takes next id depending upon the condition
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)
Helpful
Thanks!
Thanks a lot..was very helpful
thanks :)
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)
Very good! Was very helpful!
very informative THX
very thanks
Thank for all your helps
Hey hero, thank you !!