Long time past I know ... still great question and even greater explanation.
One small thing though that I'm tring to get my head around ... in the explanation , is this row correct? :
Statement: DBCC CHECKIDENT ('dbo.test',RESEED);
Next Row Identity: 5
Remarks: As Current Identity < MAX(id), this statement reseeds identity to MAX(id)
Seems to me as if Next Row Identity and MAX(ID) are swapped?
If MAX(ID) were 6, then the first ID after DBCC CHECKIDENT ('dbo.test',RESEED); would have been 7 - or am I going wrong somewhere?
But if the values in the row are swapped, then the msdn Documentation is a bit unclear ...
It only handles the conditions
Current Identity < MAX(id)
Current Identity > MAX(id)
What about Current Identity = MAX(id) as in this case?
If the RESEED hadn't taken place, then the next Insert should also have thrown an Error,
so I guess it is handled just like Current Identity < MAX(id)
Or am I completely wrong?