Duplicate Identity Values?

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Posted on 2 June 2011
Comments
Briefcase
Print

Can you have duplicate values in a field with the identity property? Of course, and this does it.

DROP TABLE dbo.MyTable

CREATE TABLE mytable
( id INT IDENTITY(1,1)
, mychar VARCHAR(10)
)
GO
INSERT mytable SELECT 'A'
INSERT mytable SELECT 'B'
INSERT mytable SELECT 'C'

SELECT * FROM dbo.MyTable

This returns these values:

Then we use Identity_insert

SET IDENTITY_INSERT dbo.MyTable ON
GO
INSERT dbo.MyTable
        ( ID, myChar )
VALUES  ( 8, -- myID - int
          'H'  -- myChar - varchar(20)
          )
SET IDENTITY_INSERT dbo.MyTable OFF

SELECT * FROM dbo.MyTable

Now we reseed and add more values

DBCC CHECKIDENT('mytable', RESEED, 4)

INSERT mytable SELECT 'E'
INSERT mytable SELECT 'F'
INSERT mytable SELECT 'G'
INSERT mytable SELECT 'H'
INSERT mytable SELECT 'I'

SELECT * FROM dbo.MyTable

You can see that we have two ID rows with “8” in them. Clearly a duplicate.

Identity doesn’t guarantee uniqueness. If you want that, make a PK or add a unique index.

Filed under: Blog Tagged: syndicated, T-SQL

Comments

Posted by Martin Catherall on 5 June 2011

I think that there is a massive misconception that identity columns means primary key - especially amongst new recruits to the IT space. I think that it's good you've pointed this out with an example.

Please register or log in to leave a comment.

Get Your Own Blog

Contact the author

All Blogs

Feeds

Archives for this blog

The Voice of the DBA