Published on

August 13, 2012

Understanding Identity Reseeding in SQL Server

Have you ever encountered a situation where you accidentally deleted all the data from your database, but when you tried to insert new data, you noticed that the identity values of your tables started from the same number as before? In this blog post, we will discuss the concept of identity reseeding in SQL Server and how it can help you reset the identity values of your tables.

Let’s take a look at a conversation between a DBA and Pinal, where the DBA faced this exact issue:

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 start 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 for such a process?”

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

Pinal: “Oh, so why didn’t 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 an extremely long and incorrect way. A very bad solution.”

DBA: “I understand, should I just take a 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 the identity beginning with 1.”

Pinal: “This is 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 the spam folder.”

Pinal: (I decided to stay silent)

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

Pinal: USE DATABASE ; EXEC sp_MSForEachTable ‘ IF OBJECTPROPERTY ( object_id ( ”?”), ”TableHasIdentity”) = 1 DBCC CHECKIDENT (”?”, RESEED, 1)’ GO

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

Watch the video on the same subject:

Reseeding the identity values in SQL Server can be a useful technique when you need to reset the identity values of your tables. However, it is important to understand the implications and use it appropriately.

By using the TRUNCATE statement instead of deleting data in a loop, you can easily reset the identity values to the original value when the data is deleted. This is a much more efficient and correct approach.

Alternatively, taking a backup of the database before inserting data and restoring it when needed can also ensure that the identity values start from 1. However, this method is not recommended as it can lead to data inconsistency and is not a best practice.

If you find yourself in a situation where you need to reseed the identity values of your tables, you can use the provided script to accomplish this task. Just make sure to understand the implications and use it wisely.

Stay tuned for our future blog post where we will dive deeper into the difference between reseeding the identity value to 1 and reseeding it to the original value.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.