Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Duplicate Identity Values?

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

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.

Leave a Comment

Please register or log in to leave a comment.