Posts
259
Comments
464
Trackbacks
17
How To: Reset Identity column in SQL Server

This is one of those simple tip posts that may seem obvious and taken for granted by those of us who have been working with SQL Server for a while now but maybe a newbie or two out there will find this helpful.

Every so often (just this morning!) I find myself resetting an identity column value back to 0 after I've deleted all the existing records so the table gets a fresh start at primary key 1. Yes, I know all about primary keys not changing and how the value in the primary key doesn't matter and so on. Sometimes I just like the primary keys starting at 1.

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Customer', RESEED, 0)

Have a day. :-|

posted on Friday, November 30, 2007 9:18 AM Print
Comments
Gravatar
# re: How To: Reset Identity column in SQL Server
Glen Gordon
11/30/2007 1:48 PM
Cool. But I think you could have also reset the identity column (and deleted faster, and skipped any triggers, etc) if you did a TRUNCATE TABLE CUSTOMER instead. Of course, this is an unlogged operation so if you were hoping for a restore, you'd be out of luck.
Gravatar
# re: How To: Reset Identity column in SQL Server
Jim
11/30/2007 2:37 PM
Great point Glen! The table I was working with this morning had all of 8 records in it so I deleted them through the UI and THEN realized I'd want to reset the identity primary key. TRUNCATE TABLE would have been a much better approach. Thanks!
Gravatar
# re: How To: Reset Identity column in SQL Server
subai
12/1/2007 2:00 AM
truncate may do the same thing fast and easy
but it won't work in some table relationship situations
Gravatar
# re: How To: Reset Identity column in SQL Server
Charles T. Blankenship
2/4/2008 11:52 AM
Thanks for this Jim, I was looking for exactly that ... same to the others who updated ... isn't Google grand!

CT
Gravatar
# re: How To: Reset Identity column in SQL Server
venkat
2/16/2008 12:14 AM
hi

thanks for showing of this information

it's working fine foe me


Thanks
venkat
Gravatar
# re: How To: Reset Identity column in SQL Server
Rajeeva
3/6/2008 2:07 AM
Thanks, its good qry
Gravatar
# re: How To: Reset Identity column in SQL Server
Michael
3/9/2008 7:05 PM
good good! works fine!
Gravatar
# re: How To: Reset Identity column in SQL Server
Sara
5/2/2008 8:16 PM
Glen truncate table is not going to reseed the table if you had already entered records into the table. All that truncate will do is clear out the records. When you enter the next record, the system is going to insert based on the highest index of the records you deleted. Enter records into a table with an identity column, then delete the data out using truncate and then insert a new record. It isn't going to start at 1. It's going to start at the last identity value + 1.
Gravatar
# re: How To: Reset Identity column in SQL Server
Nick
5/20/2008 4:49 AM
To Sara

"If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead."

http://msdn.microsoft.com/en-us/library/ms177570.aspx
Gravatar
# re: How To: Reset Identity column in SQL Server
Chitra Mahawar
7/16/2008 6:28 AM
this si about how to reset the dientity key. But what if one wants to continue with the consecutive identity key no i.e suppose there are 40 rows in the table and out of that 10 rows are deleted now if he again inserts the rows then the identity key no begins with 41. But I wnat to get it started with 31 again. How can it be possible?
Plz reply fast to my query.
Gravatar
# re: How To: Reset Identity column in SQL Server
Eric
8/15/2008 5:47 PM
Excellent, this helped thanks!
Gravatar
# re: How To: Reset Identity column in SQL Server
vishal
8/20/2008 2:39 AM
hi , it's working fine but in case when we delete all the rows from a table. but it does not work when we delete 1or more than one record.
please, supply a answer.
thanks.
best regards
vishal kumar
Gravatar
# re: How To: Reset Identity column in SQL Server
ravi
9/19/2008 4:22 AM
thanks .
Gravatar
# re: How To: Reset Identity column in SQL Server
Boeno
10/31/2008 11:52 AM
Thanks!

This method is pretty useful when implementing custom versioning or versioned transactions on sql server, and the initial primary key values is preferred to be the same after a rollback, as the sql server ROLLBACK TRANSACTION does _NOT_ set the identity seed back to what it was before the transaction started. In certain situations (like mine) I do not want to have any holes in the primary key series - and if I want to rollback transactions, this is the way to go to reset the primary key value.

A warning, this method does however require other measures in controlling concurrent access as the primary key order might get f-ed up if concurrent writes are done to the table.
Gravatar
# re: How To: Reset Identity column in SQL Server
ram
11/3/2008 6:29 AM
Thanks good job
Gravatar
# Maximum no. of instance in SQL Server
Vishwas Dwivedi
11/25/2008 12:38 AM
How many instance can we have in MS sql server
Gravatar
# re: How To: Reset Identity column in SQL Server
ankur
12/2/2008 9:49 AM
no need to truncate the table. just change table identity specifier to no and execute and again reset it to yes and execute. this will work
Gravatar
# How To: Reset Identity column in SQL Server
Darshit
1/28/2009 8:46 AM
Its realy help me and no need to set identity manualy after delete all records..Thanks...
Gravatar
# re: How To: Reset Identity column in SQL Server
mohit chauhan
2/9/2009 12:54 AM
thank you very much
it's very easy command.

thank youthank youthank youthank youthank youthank youthank youthank youthank you
Gravatar
# re: How To: Reset Identity column in SQL Server
Noha
2/25/2009 9:28 AM
Thanks u saved my time !!
Gravatar
# re: How To: Reset Identity column in SQL Server
VIKAS
3/19/2009 12:24 AM
Thanx Dost
Gravatar
# re: How To: Reset Identity column in SQL Server
Bhargavi P
3/25/2009 9:32 AM
Thank you very much.

Its working

Have a great coding.
Gravatar
# re: How To: Reset Identity column in SQL Server
Bhargavi Panchamukhi
3/25/2009 9:36 AM
Hey Thank you very much,
Working perfectly.



Gravatar
# re: How To: Reset Identity column in SQL Server
Yalidion V
4/27/2009 11:47 AM
Thanks you for publishing this information. Great,
Comments have been closed on this topic.