http://www.sqlservercentral.com/blogs/steve_jones/2011/06/02/duplicate-identity-values_3F00_/

Printed 2014/08/28 09:22PM

Duplicate Identity Values?

By Steve Jones, 2011/06/02

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:

result1

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

result2

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

result3

 

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
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.