CODEWITHSUNDEEP
sqlsql-server
Niraj Manandhar
Niraj Manandhar

Reputation: 23

Reuse the deleted row's identity value

Is it possible to reuse an identity field value after deleting rows in SQL Server 2008 Express? Here is an example. Suppose I have a table with an Id field as a primary key (identity). If I add five rows, I will have these 5 Ids: 1, 2, 3, 4, 5. If I were to delete these rows, and then add five more, the new rows would have Ids: 6, 7, 8, 9, 10. Is it possible to let it start over at 1 again?

Do I have to delete data from another table in order to accomplish this? Thanks for your help.

Upvotes: 2

Views: 7119

Answers (4)

EzLo
EzLo

Reputation: 14199

Is it possible to reuse an identity field value after deleting rows in SQL Server 2008 Express?

Yes, but it's one of the worst decisions you could make.

This is so bad that I'm not gonna explain how to do it, but just why this is bad.

We use identity columns to generate values that are unrelated to the data the table holds.

  • If we use identities as primary keys and we reuse their value, the same key will hold 2 (or more) different entities throughout time, giving place to confusion specially when generating reports or having other tables that use this key and don't have referential integrity.
  • Solutions used to identify unused values (infamous gaps and islands problem) are very slow when the amount of rows increase. This makes performance drop as rows piles up.
  • You will have to apply this "unused number" logic every time you insert rows on the table, so it's another thing to keep in mind on maintenance tasks.
  • There is little to none benefit of reusing these numbers as INT or BIGINT max values are high enough to support most business data.
  • It's common to use identities as primary keys or with a clustered index because new records will be inserted at the end, reducing fragmentation. Once records are deleted, it generates gaps in between pages (the swiss cheese pages), which is solved by doing index rebuilds. The problem comes after the rebuilds, where you will be potentially inserting new records massively in between and cause tons of page splits, hitting performance unnecessarily.

I can't possible think of a real case when you actually need to fill empty gaps in an identity column, as the purpose of this value is to be unrelated to the entity the table represents.

Upvotes: 4

venu siripuram
venu siripuram

Reputation: 1

We can do this by two ways.

If it is Non Pk Column, Insert the record with same Identity number then delete the previous identity column value.

If you don't need owl data drop the table and recreate that table or truncate the table and use DBCC CHECKIDENT to reseed your identity to the spesific value.

Upvotes: -1

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

If you are going to remove all the data inside the table you can use TRUNCATE TABLE, which will help you remove whole data in table and reset the identity to start from the SEED value.

TRUNCATE TABLE MyTable

You can also use DBCC CHECKIDENT to reseed your identity to the spesific value.

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 1);

The following code will reed the identity to start from 1.

Finally, if you need to cover the gaps, you need to know that, using IDENTITY you are mot able to cover the gaps, and you need to implement your custom function to behave like IDENTITY and cover the gaps.

Upvotes: 2

NewBee
NewBee

Reputation: 394

Possible if you remove Autoincrement attribute from ID.

You can keep a check if the table is empty or not and INSERT values accordingly.

Upvotes: -2

Related Questions