18.1.10

Peculiar nature of identity insert in a table

Peculiar nature of identify column in sql server:
I have tried to insert some records in an identity column and got confused on the below scenario. It seems to be a bug in sql server :0 I think so,

DROP TABLE Venkattemp
-- Am creating a table
create table Venkattemp (id int identity(1,1),VAL INT)
INSERT INTO Venkattemp(VAL) VALUES (1)
Truncate table Venkattemp

-- Reseeding the identity column
DBCC CHECKIDENT ('Venkattemp', RESEED, 1)
INSERT INTO Venkattemp(VAL) VALUES (1)
INSERT INTO Venkattemp(VAL) VALUES (1)

-- Checking the current identity value on the table
select IDENT_CURRENT('Venkattemp')+1
DELETE Venkattemp WHERE ID =3
select IDENT_CURRENT('Venkattemp')+1
-- Here, why its inserting value 3 in the identity column. This is an unexpected behaviour of sql server
INSERT INTO Venkattemp(VAL) VALUES (5)
SELECT * FROM Venkattemp
select IDENT_CURRENT('Venkattemp')
INSERT INTO Venkattemp(VAL) VALUES (5)
INSERT INTO Venkattemp(VAL) VALUES (5)
INSERT INTO Venkattemp(VAL) VALUES (5)
select IDENT_CURRENT('Venkattemp')
DELETE Venkattemp WHERE ID =6
select IDENT_CURRENT('Venkattemp')
-- Here, why its not inserting 6 in the below insert statement. This is an expected behaviour of sql server
INSERT INTO Venkattemp(VAL) VALUES (5)
SELECT * FROM Venkattemp

Thanks and Regards,
Venkatesan Prabu .J

1 comment:

  1. it seems to be fine for me
    IDENT_CURRENT('temp') returns the current identity value and the next value will be "IDENT_CURRENT('temp')+1 " in your case which will be 6+1 = 7 , which is what it is inserting

    ReplyDelete