The Nuance of DBCC CHECKIDENT That Drives Me Crazy

When I put together my DBCC presentation a couple years ago I created a demo for the CHECKIDENT command.  I had used it a few times and figured it was a pretty straight-forward command.  In truth, it is, but there is one thing that I don’t find intuitive about it.  And maybe I’m the only one, but just in case, I figured I’d write a quick post about it.

CHECKIDENT is used to check the current value for an identity column in a table, and it can also be used to change the identity value.  The syntax is:

DBCC CHECKIDENT
 (
   table_name
     [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
 )
[ WITH NO_INFOMSGS ]

To see it in action, let’s connect to a copy of the AdventureWorks2012 database and run it against the SalesOrderHeader table:

USE [AdventureWorks2012];
GO

DBCC CHECKIDENT ('Sales.SalesOrderHeader');

In the output we get:

Checking identity information: current identity value '75123', current column value '75123'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Hooray, seems pretty basic, right?  Well, did you know that running the command as I did above can change the identity seed if the identity value and column value do not match?  This is what I meant initially when I said it wasn’t intuitive.  I didn’t include any options with the command, therefore I do not expect it to make any changes.  In fact, you have to include an option to ensure you do not make a change.  Let’s take a look.

First we’ll create a table with an identity column and populate it with 1000 rows:

USE [AdventureWorks2012];
GO

CREATE TABLE [dbo].[identity_test] (
   id INT IDENTITY (1,1),
   info VARCHAR(10));
GO

SET NOCOUNT ON;
GO

INSERT INTO [dbo].[identity_test] (
   [info]
   )
   VALUES ('test data');
GO 1000

Now we’ll run CHECKIDENT, as we did above:

DBCC CHECKIDENT ('dbo.identity_test');

Checking identity information: current identity value '1000', current column value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Our results are what we expect.  Now let’s reseed the identity value down to 10:

DBCC CHECKIDENT ('dbo.identity_test', RESEED, 10);

Checking identity information: current identity value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The output doesn’t tell us specifically that the identity has been reseeded, so we’ll run CHECKIDENT again, but this time with the NORESEED option (different than what we ran initially):

DBCC CHECKIDENT ('dbo.identity_test', NORESEED);

Checking identity information: current identity value '10', current column value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now we can see that the identity value and the current column are different, and because we included the NORESEED option, nothing happened.  And this is my point: if you do not include the NORESEED option, if the identity and column values do not match, the identity will reseed:

--first execution
DBCC CHECKIDENT ('dbo.identity_test');
PRINT ('first execution done');

--second execution
DBCC CHECKIDENT ('dbo.identity_test');
PRINT ('second execution done');

Checking identity information: current identity value '10', current column value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
first execution done

Checking identity information: current identity value '1000', current column value '1000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
second execution done

So just in case I’m not the only one for whom this isn’t obvious: Make sure to include the NORESEED option when running DBCC CHECKIDENT.  Most of the time, the identity value probably matches the value for the column.  But that one time where it doesn’t, you may not want to reseed it…at least not right away.

9 thoughts on “The Nuance of DBCC CHECKIDENT That Drives Me Crazy

  1. That would explain SO much. I didn’t know this and used it recently (without NORESEED) in order to check on the identities in a Merge Replicated table. Could explain why it suddenly went crazy.

    They were different values when I first ran it, but that was correct for my purposes. Next thing I knew there were replication errors all over the place.

    Pretty sure this would be it.

    Thanks!!!

  2. Kevin-

    It definitely sounds like CHECKIDENT reseeded the identity for you – so sorry that it happened! And with replication, too, I bet that was interesting to sort out.

    Thanks for reading!

    Erin

  3. Thanks for this article, Erin! This wasn’t obvious to me today either and I just checked it for a client today and last week and the issue was fixed but I didn’t realize it re-seeded itself. Then I did a Google search and found your article – it makes sense.

  4. Every DBA and database developer should be aware of this!

    However, this happens when you reseed to a value that is lower than the present value.
    If reseeded to a higher value, it stays intact.

    Code to try:

    DBCC CHECKIDENT (‘dbo.identity_test’, RESEED, 1010);

    –first execution
    DBCC CHECKIDENT (‘dbo.identity_test’);
    PRINT (‘first execution done’);

    –second execution
    DBCC CHECKIDENT (‘dbo.identity_test’);
    PRINT (‘second execution done’);

  5. I can confirm this is still the case with SQL 2016 SP1. You still need to use the NORESEED option.

    I’m about to run out of Int values, but have removed the first 1.5 Bil rows, so am reseeding back to 0.

    Drove me crazy until I read your Blog.

    Thanks for posting.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.