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

Identity Reseeding

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

Comments

Posted by Jason Yousef on 4 June 2011

Great article, thanks Steve, I learned something!

Posted by Todd.Everett on 10 June 2011

Very timely Steve - thanks.  I just recently suffered from this misunderstanding on one of our tables and your post summarizes nicely.  Into the briefcase!

Leave a Comment

Please register or log in to leave a comment.