Check out the following script that creates and populates a table
The output of this what you would expect from a well-behaving Identity field.
Now let's force a couple of values into the Identity column.
The output is
Where did RegionId 1 go?
The answer becomes more clearer if you look at the current value of the identity using
run this after the creation of the table and you get
meaning, it's currently at 'null' but I'll be incrementing in 1's so the next value will be 1
Then after forcing the insert with
because you've stated that this identity will start at 1. You've added rows, but haven't gone past 1 with the value for the identity field, so the current value is the same as if you've added one row 'normally', hence the next value is 2.
The reason for that is that the next value is always the value of the greatest value, or seed start value + 1 (or whatever increment you've defined), in other words (max of -99, -9, 1) = 1 + 1 = 2.
In the same way you could add 2 rows 'normally' and then reseed at 100 : (max of 1,2,100) = 100 +1 = 101 would be the next identity value.
You can use the following to check the current identity seed:
Run that right after you create the table and you can see it's NULL--no identity has been seeded. After you perform inserts, though, it defaults to the first value, which is 1 (because you seeded the identity at 1). Do the inserts and after the two records are inserted, run the check statement again and you'll see that up to 3, which is your last inserted value.
I think you're seeing a side effect from the way that identity_insert works: it looks like it guarantees that the current value for an identity isn't NULL after a successful insert. If you pre-seed a region before doing your identity inserts, you'll see that 1 isn't skipped:
drop table dbo.Region;
answered Jun 16 '11 at 01:18 PM