Reuse Identity Value After Deleting Rows

Reuse identity value after deleting rows

You can use the following to set the IDENTITY value:

DBCC CHECKIDENT (orders, RESEED, 999)

That means you'll have to run the statement based on every DELETE. That should start to highlight why this is a bad idea...

The database doesn't care about sequential values - that's for presentation only.

Reuse the deleted row's identity value

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.

will ms sql server reuse deleted primary keys in auto increment mode?

MSSQL will not re-use the primary keys that have been deleted using DELETE (I'm assuming you are talking about the identity incrementation.) If you TRUNCATE the table it will reset the seed and reuse them.

If you go over the max for INT it will indeed just fail to make the next row. You can convert your INT column to BIGINT to avoid that.

BIGINT has a max of: 9,223,372,036,854,775,807 and INT has a cap of 2,147,483,647 but note.. you can also use those negative values too!

You can read about those caps here: https://learn.microsoft.com/fr-fr/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

How can I delete a database record and reuse the deleted primary key?

I presume you are using auto-increment to assign a new key value to your Id column. You are assuming that since there are no records then the next free value would be 1, but it just keeps incrementing from the previous highest value.

Its a bad idea, but if you really wished to recycle your key values then you could switch off auto-increment and manually manage your key values yourself, but this is error prone and difficult.

Do you really need to do this? Int and BigInt can hold very large numbers, are you likely to ever run out of key values so that recycling might be required?

If you just want to reset your auto-increment back to 1 I suggest you look at this post

c# entity frame work how to set automatic ID value after delete a row

maybe its work

var toBeDeleted = (int)dataGridView1.SelectedRows[0].Cells[0].Value;
            var userdata = db.ScoutDatas.Where(c => c.DataID >= toBeDeleted).Take(2).ToList();
            db.ScoutDatas.Remove(userdata[0]);
            userdata[1].DataID = toBeDeleted;
            db.SaveChanges();
            dataGridView1.Refresh();

Why are my SQL Server identity values being reused?

Identity values will be unique within the table as the moment it is needed.

The SQL engine has no knowledge of old identity values if you truncate the table.

If you want a value that will only ever be used once during the lifetime of a table you should consider a GUID or create a unique key based on identity + datetime.

Do numerical primary keys of deleted records in a database get reused for future new records?

NO. numerical primary keys will not reused, except you specify them manually(you should really avoid this!)

how to reuse deleted primary keys in mysql?

Using:

ALTER TABLE [your table name here] AUTO_INCREMENT = 1

... will reset the auto_increment value to be the next based on the highest existing value existing in the table. That means it can't be used to correct gaps of more than one.

The only reason to do this would be for cosmetic ones - the database doesn't care if records are sequential, only that they relate to one another consistently. There's no need to "correct" the values for the database's sake.

If you are displaying the id values to the user, which is why you'd like them to always be sequential, then I'd recommend adding a surrogate key. Use the surrogate key for displaying to the user, so the values can be re-sequenced as needed but referencial integrity is otherwise unaffected. The surrogate key in this case would be an integer column.

Will MySQL reuse deleted ID's when Auto Increment is applied

InnoDB resets the auto_increment field when you restart the database.

When InnoDB restarts, it finds the highest value in the column and then starts from there.

This won't happen in MyISAM because it caches the last incremented id.

Update

This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,

Table t1 has an auto-inc primary key.
Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.
As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.

This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB
persistent max value for autoinc columns.
InnoDB will keep track of the maximum value and on restart preserve
that max value and start from there.

Reuse Identity Value After Deleting Rows