SQL Server 2005 Identity Specification not respecting seed value

Question

I am in the process of converting an Access database to SQL Server 2005. I have successfully migrated the data and original schema using SSMA and am now in the process of normalizing the database, which requires me to add a few unique identifiers.

Some of the columns we had were previously created using an AutoNumber data type, which is fine. However, I need to create meaningless but unique identifiers for other data, so I am using the int data type with the Identity Specification property. I am seeding at '101' to keep this data above the range that currently exists for data that already has unique identifiers, as they will eventually reside in the same table.

My problem is that when I create a new int with Identity Specification with a seed value of '101' and an increment of '1', the numbers start at '1'. I have attempted to reseed with:

USE dbMyDatabase
DBCC checkident(tblMyTable, reseed, 101)

to no avail. Any suggestions would be greatly appreciated. Thanks in advance!

Answer 1

The solution was to create the column by using a SQL query manually. Adding it through the "New Column..." option produced incorrect results every time. Now that I added it with

USE dbMyDatabase
ALTER TABLE tblMyTable 
ADD fldID INT IDENTITY(101,1)

it works just fine.