Often we come across to such situation where we will be having more than a way to overcome the issue but the instance might be confusing us to choose the best one. One of my Friend called me this night and asked me suddenly ‘Can you Help me?’ without saying ‘Hello’. Though I said Hello to him first!
My Friend: Mistakenly I have deleted and inserted a new row in a table, which has an Identity column. Deletion of data does not matter to me cause this’s a DEV environment but the reseeding of identity column does matter to me now.
I: I understood him certainly and replied.. Yes I can help you but need a min time to execute your scenario in my environment.
My Friend: Ok take 5 min time.
Game began! something like this..
Suggested him to delete the recent inserted record from the table and reinsert it manually with very next identity value available in the identity column by setting IDENTITY_INSERT ON then reseed the IDENTITY column with max value.
Here the code goes.. during my testing
--Table creation and record deletion
create table MyIdentity
(ID int identity(0,1),
Name varchar(12)
)
insert into MyIdentity (Name)
values ('One')
GO 10;
select * from MyIdentity
delete from MyIdentity where id in (8, 9)
Till here the situation says the next insert will go on identity value 10 because we have already used 8 and 9. Here the actual thriller starts
--Value Inserted manually
set identity_insert myidentity on
insert MyIdentity (ID, Name)
values (8, 'Eight')
set identity_insert myidentity off
--IDENTITY value will be reseeded here.
declare @maxid int
set @maxid = (select max(id) from MyIdentity)
dbcc checkident('MyIdentity', reseed, @maxid)
My Friend: Thank you!
