sqlfriends (10/20/2012)
I see on dev server, it starts from 5000001, but on prod, it starts from 5000000.The only diffrence is on dev, I run 1 and 2 in one batch file. On prod, I run 1 yesterday, and today I run 2.
Are you sure things are happening in the same order?
The rules for what value you get first are something like:
If you set the the identity to 5000000 when the file is empty and has never contained any records, then insert the new records, then insert the old records using identity insert, the new records will start at 5000000. The same applies if you empty the file using truncate table before setting the identity value.
If you insert the old records using identity insert first (or any record), then set the identity to 5000000, then insert the new records, the new records will start at 5000001. Even if you delete all the new records before you start inserting the new ones, unless you do the delete using truncate table, the new records will start at 5000001.
But the text in BoL has changed between 2008 and 2012, so I'm not very sure. Nor do I trust the text to be accurate (BoL usually is, but by no means always).
Tom