How to reset the Identity Value if any value deleted in mid or atlast.

Question

 AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

 

 

CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES (’First’)
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete records from middle and bottom both*/
DELETE
FROM TableID
WHERE ID IN (4,7,11,12)
Go
/* Check the records in table */
SELECT *
FROM TableID
GO
/*
Now check the missing identity values
To Do so we will create a memory table with a sequential number,
from 1 to the ident_current value and join that to the live table.
*/
Declare @rows Int
Declare @numberOfRowsToInsert Int

declare @seq table (
seq int not null primary key
)
/* Get current Max Value and reseed table */
SELECT @rows = MAX(ID) FROM TableID
DBCC CHECKIDENT(’TableID’, RESEED, @rows)
set @rows = (select Ident_Current(’TableID’)) + 5
/* Insert values into Memory table */
declare @i int
set @i = 1
while @i <= @rows
begin
insert @seq values( @i )
set @i = @i + 1
end
/* Now set the Identity Insert On, so that we can insert any number */
Set Identity_Insert TableID On

INSERT INTO TableID(Id,col) Select seq,’Third’
From @seq left outer join TableID T on seq = T.ID
Where T.ID Is Null

Set Identity_Insert TableID OFF
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

---

Ravishankar Maduri MCTS,MCPD,MCP

Answer 1

Hello Ravishankar ,

This forum provides support for documentation and interoperability questions on open specifications.
The Open Protocol Specifications can be found at: http://msdn2.microsoft.com/en-us/library/cc203350.aspx.
Your post does not appear to be related to the Open Protocols Specifications documentation set.

You may want to try one of our other  forums to get you started finding what you need. I think the below group of forums  would be a good place to start:

 http://social.msdn.microsoft.com/Forums/en-US/category/sqlserver


Thanks
John Dunning
Senior Escalation Engineer Microsoft Corporation
US-CSS DSC PROTOCOL TEAM