Koen Verbeeck (10/22/2012)

---

Lokesh Vij (10/22/2012)

---

kapil_kk (10/22/2012)

---

in this case the max ident value would be 101 as with first insert statement the value of ident was 100 and with another insert statement the ident value would be 101 and max from 101 and 100 is 101 so 101 is the max value..

hope its clear to you

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??

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.