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.

Filed under: Blog Tagged: syndicated, T-SQL