Truncating a table with an Identity column and DBCC CHECKIDENT

I did not know this behavior and came across it recently while resetting audit tables in my development database for a test deployment.

Given:

A table that contains an identity field that you run the following command against:

DBCC checkident ([Audit_Logins]);
The result is:
Checking identity information: current identity value '86', current column value '86'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Assuming the table has no relationships to prevent it from being truncated, you run the following command:

TRUNCATE TABLE [dbo].[Audit_Logins];

What is the result when you run DBCC CHECKIDENT against it again?

  • 87
  • 86
  • 0
  • Null

The correct answer is Null.

The result of the DBCC CHECKIDENT is

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

From BOL 2014:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

https://msdn.microsoft.com/en-us/library/ms177570.aspx

 

I sent it in to SQL Server Central and it was accepted as last Thursday’s Question of the Day!  I was happy.  I should have provided a table and data load script, more importantly I should have included a statement that the table was created with an IDENTITY(1,1).  Something to remember if I ever submit another question.  Still, there was good discussion.  63% (as of this post) got the answer wrong.

Based on discussions, I think the reason behind the null is that after a truncate, there are null records in the table, so it reports the identity value as null rather than Seed – 1.

I thought it was interesting.  Then again, I’m easily amused.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s