Category: Blog

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.

I love the identity property. I use it in many of my tables, mostly because it gives me a fairly reliable surrogate key that I can use in my tables, especially when testing something. I often do something like this:

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

In this table, I have these results:

id          mychar

———– ———-

1           A

2           B

3           C

If I then look to reseed things for some reason, maybe I want to leave a gap somewhere, I can do this:

SET IDENTITY_INSERT MyIdentityTest ON
INSERT MyIdentityTest (id, mychar) SELECT 12, 'L'
GO
SET IDENTITY_INSERT MyIdentityTest OFF
GO

This means my table now looks like this.

id          mychar

———– ———-

1           A

2           B

3           C

12          L

If I insert a new value:

INSERT MyIdentityTest SELECT 'M'
GO

And now check all the results, I have this:

SELECT * FROM myidentitytest

we get

id          mychar

———– ———-

1           A

2           B

3           C

12          L

13          M

 

Suppose I realized that I had a problem and decided to “fix” my identity values. I can reseed like this, which sets the identity property tracker back to 3.

DBCC CHECKIDENT(myidentitytest, RESEED, 3)
go
INSERT MyIdentityTest SELECT 'D'
GO

However look at the results of the insert. It shows 4 instead of 3, which is what I want in this case.

SELECT * FROM myidentitytest

id    mychar

—– ———-

1     A

2     B

3     C

12    L

13    M

4     D

Quite a few people think that if I set the identity to “3”, I should have 3 as the next value. That’s not the case, and it’s something to be aware of when working with identities. If you are looking to fill gaps or move your seed for some reason (like merge replication), understand that you are inserting the “last” value as your seed, not the “next” one.