SQL SERVER – Reseting Identity Values for All Tables

Sometime email requesting help generates more questions than the motivation to answer them. Let us go over one of the such examples. I have converted the complete email conversation to chat format for easy consumption. I almost got a headache after around 20 email exchange. I am sure if you can read it and feel my pain.

DBA: “I deleted all of the data from my database and now it contains table structure only. However, when I tried to insert new data in my tables I noticed that my identity values starts from the same number where they actually were before I deleted the data.”

Pinal: “How did you delete the data?”

DBA: “Running Delete in Loop?”

Pinal: “What was the need of such need?”

DBA: “It was my development server and I needed to repopulate the database.”

Pinal: “Oh so why did not you use TRUNCATE which would have reset the identity of your table to the original value when the data got deleted? This will work only if you want your database to reset to the original value. If you want to set any other value this may not work.”

DBA: (silence for 2 days)

DBA: “I did not realize it. Meanwhile I regenerated every table’s schema and dropped the table and re-created it.”

Pinal: “Oh no, that would be extremely long and incorrect way. Very bad solution.”

DBA: “I understand, should I just take backup of the database before I insert the data and when I need, I can use the original backup to restore the database. This way I will have identity beginning with 1.”

Pinal: “This going totally downhill. It is wrong to do so on multiple levels. Did you even read my earlier email about TRUNCATE.”

DBA: “Yeah. I found it in spam folder.”

Pinal: (I decided to stay silent)

DBA: (After 2 days) “Can you provide me script to reseed identity for all of my tables to value 1 without asking further question.”

Our conversation ended here. If you have directly jumped to this statement, I encourage you to read the conversation one time. There is difference between reseeding identity value to 1 and reseeding it to original value – I will write an another blog post on this subject in future.

This is also a nice script to generate SQL statements. Set output of the SQL to “Text”

SELECT ‘
GO
DBCC CHECKIDENT(”ps.’ + TabelNaam + ”’,RESEED,0)’

FROM
— LT: Linked Tables
(SELECT O.name AS TabelNaam
, S.name AS SchemaName
FROM sysobjects AS O
LEFT OUTER JOIN sys.schemas AS S ON O.uid = S.schema_id
WHERE O.type = ‘u’ AND O.uid=5
AND CHARINDEX(‘_’,O.name)=0
–ORDER BY O.[name]
) AS LT
WHERE SchemaName = ‘dbo’
ORDER BY TabelNaam

Truncate will not be allowed if the user has any referential integrity in place on the tables to be truncated. In this case you would need to disable any integrity before truncating and even loading the tables

what we have to do when any one uses the identity column as primary key, and referenced to other table(foreign key) then how can we re-generate the Identity column, without changing the relationship of columns as well as data.

on execution of the above sp am able to delete the data. Can use truncate instead of ‘Delete’ so that it will automatically reseed the identities or shall i include the query u provided at the end of the sp?

Hi, I want to ask..
how do I use an identity increment again after i delete it without deleting the other rows?
For example, the last identity is 151, and i added 152, but i delete the 152,…
so since the last row still 151, how to make it become 152 again not 153, when i add a new row again.
???

Suppose, I’ve to delete all the records except the very first record. In that case how can I reseed the values against maximum ids. Please keep in mind, by deleting one record, data is not deleted from only one table, it is deleted from multiple tables.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.