Reset Identity Seed After Deleting Records in SQL Server

Reset identity seed after deleting records in SQL Server

The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Example:

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

It was not supported in previous versions of the Azure SQL Database but is supported now.

---

Thanks to Solomon Rutzky the docs for the command are now fixed.

Reset IDENTITY column whitout deleting all rows

Using @Killer Queen suggest, I have solved using this snippet of code, which find the MAX([Idx]) of MyTable after the DELETE and the reeseed the identity before the new INSERT.

It works because the rows with [Permanent]=1 are the first rows inserted in the table so their [Idx] values start from 1 and are very low.

DELETE 
FROM [MyTable]
WHERE [Permanent]!=1 OR [Permanent] IS NULL

DECLARE @MaxIdx As bigint
SET @MaxIdx = (SELECT ISNULL(MAX([Idx]),0) FROM [MyTable])

DBCC CHECKIDENT ('MyTable', RESEED, @MaxIdx); 

SQL Server: reset identity on any delete action

You can create a DELETE TRIGGER and then use

SQL Fiddle Demo

CREATE TRIGGER sampleTrigger
    ON dbo.test
    FOR DELETE
AS
    DECLARE @maxID int;

    SELECT @maxID = MAX(id)
    FROM test;

    DBCC CHECKIDENT (test, RESEED, @maxID);
GO

So next insert will use 4.

But again this isnt something you should really be worry about it

Reset existing data ids and identity after deleting data?

If you want to fill in the gaps, you will have to do it manually, using Set identity_insert on.

For example, if I want to change my rows 51-60 to be 1-10:

set identity insert mytable on
insert mytable(id, column1, column2)
select id-50, column1, column2 
from myTable 
where id between 51 and 60
set identity_insert mytable off
delete mytable where id between 51 and 60

Then reseed your ID and it should be good to go.

Reset identity seed after in SQL Server in table filled with records

The problem is your understanding; the code is very likely working exactly as it is supposed to, and as I demonstrated.. RESEED resets the value of the next IDENTITY generated, it doesn't change any of the existing values.

Take SQL similar to what I gave in the comments:

CREATE TABLE dbo.Person (ID int IDENTITY(1,1), AbligatoryColumn char(1));
GO

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT N1.N
    FROM N N1, N N2, N N3)
INSERT INTO dbo.Person (AbligatoryColumn)
SELECT 'a'
FROM Tally;
GO

SELECT TOP (5) *
FROM dbo.Person
ORDER BY ID ASC;

If you run this you get the follow results:

ID          AbligatoryColumn
----------- ----------------
1           a
2           a
3           a
4           a
5           a

Now, let's RESEED the table, and INSERT another row:

DBCC CHECKIDENT ('dbo.Person', RESEED, 0)
GO
INSERT INTO dbo.Person
DEFAULT VALUES;
GO
SELECT TOP (5) *
FROM dbo.Person
ORDER BY ID ASC;

This gives the following data set:

ID          AbligatoryColumn
----------- ----------------
1           a
1           NULL
2           a
3           a
4           a

Notice that there are 2 rows where ID has a value of 1. This is because the new row we inserted has used thenew seed, so the next value generated was 1 (as when you RESEED you are defining the last value used, not the next value to be).

Note that you can't UPDATE the value of an IDENTITY, so if we tried the following you would get an error:

UPDATE dbo.Person
SET ID = ID + 1000
WHERE AbligatoryColumn = 'a';

Cannot update identity column 'ID'.

The real question why do you want to change the value? An IDENTITY is just an arbitrary value, it's value doesn't matter.

If you "must" (and I would suggest you don't need to) you would need to CREATE a new table, INSERT the data from your existing table into it (likely with IDENTITY_INSERT enabled) and then DROP your old table, and rename the new one. If you have any foreign key constraints pointing to your current table, you'll need to DROP all of these, and I really hope you have any data referencing the existing ID values, as you'll then need to update all the foreign key values before you recreate the foreign key constraints. As a result your new table would (albeit likely temporarily) need to have both the old and new PK values in separate columns.

So, in truth, leave it as it is; the value of an IDENTITY literally doesn't matter. It's an arbitrary value and it whether the first value starts at 1, 17, or -167 or if there are numbers "missing" is irrelevant to functionality of what IDENTITY is there to achieve; an always ascending value.

reset id(primary key) after deleting record in sql

Well this is not a good idea if the id is associated with other tables it will make a big mess in your database, however I do not know in which context you want to use this so I leave it to your discretion.
But instead of deleting you can disable the auto incrementer of the table in question, and write a bit to add the free ids.
Obs; Continues to be a bad idea!

Resetting IDENTITY column after deleting one or two is a good practice in SQL Server

The official Microsoft statement is that you should expect gaps when using IDENTITY, so I would infer its absolutely fine if your deletions cause the gap. There is no reason to fill in the gap

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Since that is a very long thread on a tangentially related topic, relevant quote below:

As documented in books online for previous versions of SQL Server the
identity property does not guarantee the absence of gaps, this
statement remains true for the above workarounds. These solutions do
help with removing the gaps that occur as part of restarting the
instance in SQL Server 2012.

Reset the numeric sequence of a column after deleting a row

Instead of resetting (a potentially significant part of) the column values when deletion happen, you could build a view view on top of the table that computes the sequence on the fly.

Either using a sort on the user name (however this means that the sequence could change when a usernmae is updated):

CREATE VIEW myview AS
SELECT
    group,
    user,
    ROW_NUMBER() OVER(PARTITION BY group ORDER BY user) sequence
FROM mytable t

Or better yet, with a sort based on the original sequence:

CREATE VIEW myview AS
SELECT
    group,
    user,
    ROW_NUMBER() OVER(PARTITION BY group ORDER BY sequence) sequence
FROM mytable t

---

Related Topics