kapil_kk (10/22/2012)

---

Execute this code and you will get duplicate values in Identity values--

CREATE TABLE IdentTest
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)

INSERT INTO IdentTest VALUES ('xyz') -- <== Added to original
INSERT INTO IdentTest VALUES ('123') -- <== Added to original

SELECT Ident FROM IdentTest
-- Result 1,2

DBCC CHECKIDENT ('IdentTest',RESEED,100)
--Checking identity information: current identity value '2', current column value '100'.

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest
--Result 1,2,101

DBCC CHECKIDENT ('IdentTest',RESEED,100)
--Checking identity information: current identity value '101', current column value '100'.

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest
--Result 1,2,101,101

Its intresting to know that functionality of identity is exploiting in this way

Lokesh Vij (10/22/2012)

---

Actually the question posted by John is little different. Execute the below code:

CREATE TABLE IdentTest 
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
)

INSERT INTO IdentTest VALUES ('xyz')  -- <== Added to original
INSERT INTO IdentTest VALUES ('123')  -- <== Added to original

DBCC CHECKIDENT ('IdentTest',RESEED,100)

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest -- <== (done away with max)

Now the last select statement (done away with max statement intentionally), returns three values 1,2 and 101
Why value 101?? Why not 100 or 102??

Koen Verbeeck (10/22/2012)

---

If data is present in the table before DBCC CHECKIDENT, the new identity value is the reseed value + current incremenet. In this case, 100 + 1 = 101.

Anyway, nice question.

CREATE TABLE IdentTest 
( --Ident INT NOT NULL IDENTITY (1,1)
 varfield varchar(100)
)

INSERT INTO IdentTest VALUES ('xyz')
INSERT INTO IdentTest VALUES ('123')

Select * from IdentTest

ALTER TABLE IdentTest
	Add  Ident INT IDENTITY (1,1)

INSERT INTO IdentTest VALUES ('abc')

SELECT Ident FROM IdentTest
 where varfield = 'abc'