DBCC CHECKIDENT and how it works with DELETE/TRUNCATE

Some weeks back I wrote about the differences between truncating and deleting data from a table. Today I’m going to look into the behaviour of an identity column on a table when you use either a delete or a truncate to clear the data out of the table.


-- note I am reseeding a non empty table for demo purposes only!
-- from the Books Online page for DBCC CHECKIDENT;
-- If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:

-- If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

-- If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

USE [msdb];
GO

CREATE TABLE [dbo].[ident_demo_1] (
id tinyint IDENTITY (1,1),
demo_1 VARCHAR(20));
GO

SET NOCOUNT ON;
GO

-- use a tinyint and max out table
-- you could do this with any of the ints, but let's save ourself some time
-- and use the TINYINT!
declare @i int = 1;
while @i <= 255
begin
INSERT INTO [dbo].[ident_demo_1]
values
( 'ident_demo_' + cast (@i as varchar (3)))
set @i = @i + 1;
end;

Ok, so the data is set up for the first demo. The first demo is about using DBCC CHECKINDENT.


--DBCC CHECKINDENT will the current identity value and the current column value
DBCC CHECKIDENT ('dbo.ident_demo_1');

--use the "DBCC CHECKINDENT" to reset the identity down to 10
DBCC CHECKIDENT ('dbo.ident_demo_1', RESEED, 3);

--check again, this time using the "NORESEED"
DBCC CHECKIDENT ('dbo.ident_demo_1', NORESEED);

--run these commands below together; you'll see the "current identity column" change from 3 to 255
--this is because if the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.
--so you have to explicitly omit the altering of the values using NORESEED

DBCC CHECKIDENT ('dbo.ident_demo_1');

DBCC CHECKIDENT ('dbo.ident_demo_1');

--in order to keep the identity value the same, or if you just want to check the value, use the NORESEED argument

drop table [dbo].[ident_demo_1]

CHECKIDENT certainly is keen! It’s not quite correct to call it “CHECKIDENT”, because if it find an anomaly between the current identity value and the maximum value it will correct this. It should be called “CHECKANDCORRECT”!

Set up a second demo table:


-- 2

USE [msdb];
GO

CREATE TABLE [dbo].[ident_demo_2] (
id tinyint IDENTITY (1,1),
demo_1 VARCHAR(20));
GO

SET NOCOUNT ON;
GO

declare @i int = 1;
while @i <= 25
begin
INSERT INTO [dbo].[ident_demo_2]
values
( 'ident_demo_' + cast (@i as varchar (3)))
set @i = @i + 1;
end;

The second demo shows what happens tothe identity column when we use “delete” to clear all the data.


--using noreseed argument, check the identity
DBCC CHECKIDENT ('dbo.ident_demo_2', NORESEED);

--delete all the data!
delete from [dbo].[ident_demo_2]
where id = 255

--check the identity again; the column value is now null, which is not 0 but rather "unknown"
--note that not specifying the argument NORESEED does not make any difference here as the current column value is "null" which is neither greater nor less than 255
DBCC CHECKIDENT ('dbo.ident_demo_2');

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

--inserting into the table wil lfail, because the max ident number is the max value which can be inserted into a tinyint
INSERT INTO [dbo].[ident_demo_2] VALUES ('epic fail');
GO

--if we reseed the number back down to a very low number, we will be able to reinsert
DBCC CHECKIDENT ('dbo.ident_demo_2', RESEED, 3);

--cehck the identity again; yes, noreseed is not required (try it yourself) but best get into good habits now..
DBCC CHECKIDENT ('dbo.ident_demo_2', NORESEED);

INSERT INTO [dbo].[ident_demo_2] VALUES ('epic win');
GO 10

--current columns value will now take the max current value of the identity column
--so when emptying a table using a delete statement-
--until we explicitly reseed the identity value, it will retain the maximum value
--the current column value will be "null" and will stay like this until we reinsert
DBCC CHECKIDENT ('dbo.ident_demo_2');

drop table [dbo].[ident_demo_2]

So to recap: if all the data in a table is cleared completely using a delete, then the identity column needs to be explicitly reset to start from the beginning.

Now the third demo table;


--3

USE [msdb];
GO

CREATE TABLE [dbo].[ident_demo_3] (
id tinyint IDENTITY (5,1),
demo_1 VARCHAR(20));
GO

SET NOCOUNT ON;
GO

declare @i int = 1;
while @i <= 250
begin
INSERT INTO [dbo].[ident_demo_3]
values
( 'ident_demo_' + cast (@i as varchar (3)))
set @i = @i + 1;
end;

The final demo will check the behaviour of the values of an identity column when the data has been truncated:


--maxxed out identity value and current column value
DBCC CHECKIDENT ('dbo.ident_demo_3', NORESEED);

--delete data using the "truncate" command
truncate table [dbo].[ident_demo_3]

--this time, both the identity value and the current column value are set to NULL
DBCC CHECKIDENT ('dbo.ident_demo_3', NORESEED);

--can we can go ahead and insert directly into the table without having to reseed..?
INSERT INTO [dbo].[ident_demo_3] VALUES ('epic win');
GO 10

--yes we can! plus, we can see that the values are reset back to the original values specified when creating the table
DBCC CHECKIDENT ('dbo.ident_demo_3', NORESEED);

--you can test this yourself by dropping and recreating this table with the ident starting on a number greater than 1
drop table [dbo].[ident_demo_3]

So the difference between a DELETE and TRUNCATE is the resetting of the identity value; with a delete, it stays at the maximum value and will resume using the maximum value when we resuming inserting. However with a truncate, the identity value is set to NULL and starts back at the first value that was specified when creating the identity property.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “DBCC CHECKIDENT and how it works with DELETE/TRUNCATE”

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