Duplicate Values in Identity Column

Question

I've got a table which has a column named id which is of type Identity. But this column contains duplicate values 1..8 and then again 1..10

How in the world is this possible?

Answer 1

I tested what Giogri says and if you enable the Identity Specification (at least on 2008, probably other versions, too) after the table has rows, the DB will start the numbering at the highest integer value. If you have one row with 100 as the column value, then enable Identity, the next insert will be 101. Even with Identity Seed specified as 1. Wasn't what I would have expected, but its what happened.

In addition to SET IDENTITY INSERT, there's also a reseed command. DBCC CHECKIDENT command which will reset your identity values back to what you specify.

Given that enabling the Identity Specification actually starts at the highest integer in the column, someone probably either used SET IDENTITY_INSERT or did a DBCC CHECKIDENT.

The quickest way to resequence as Andomar says is to drop/recreate the column like this

ALTER TABLE tbl
DROP COLUMN ident_column
GO
alter TABLE tbl
ADD ident_column int IDENTITY

SET IDENTITY_INSERT docs: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx
DBCC CHECKIDENT docs: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx