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.