Friday, March 07, 2008

Identity starts from 0 instead of 1. SQL Server bug?

We experience a strange problem. Identity field of a table is set to [1,1] by generating script. Every time, when we drop the whole database, and then re-create it by running script in SQL Server 2008 Management Studio, and then insert a record into that table programmatically (C# code), SQL Server set identity field value of that first record to 0 instead of 1!
Then we also programmatically delete all records from that table and then execute
DBCC CHECKIDENT('" + targetTableName + "', RESEED, 0)

All next runs of the same C# function inserts records correctly, with identity field starting from 1 as expected.

So, again, identity field does not want to behave correctly if database was just created by script. After inserting a record and deleting it, everything works fine.

Is it SQL Server 2008 bug? Any idea on possible workaround?