DBCC CHECKIDENT RESEED behaves differently when TRUNCATE or DELETE was used on the table

Let’s assume we have some script where tables are filled with data and then DELETED/TRUNCATED again to rerun the script. Some of these tables are large and they don’t have a foreign key reference so we will decide to use TRUNCATE to remove the data. For tables referenced by foreign keys, we will use DELETE instead (because TRUNCATE can’t be executed on tables referenced in foreign keys). All the tables are using IDENTITY(1,1) property and we will use DBCC CHECKIDENT to RESEED identity values to be starting again from 1. But what is our surprise if we will discover later, that for some tables the first identity value is 1 and for other tables it’s starting from 2?

Let’s demonstrate it.

We will create two tables and fill in some data. These tables are absolutely the same including the IDENTITY specification. The only difference is their name.

DROP TABLE IF EXISTS [dbo].[To_Be_Deleted]
DROP TABLE IF EXISTS [dbo].[To_Be_Truncated]
GO

CREATE TABLE [dbo].[To_Be_Deleted] (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)
GO
CREATE TABLE [dbo].[To_Be_Truncated] (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)
GO

INSERT INTO [dbo].[To_Be_Deleted] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Deleted] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Deleted] DEFAULT VALUES
GO
INSERT INTO [dbo].[To_Be_Truncated] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Truncated] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Truncated] DEFAULT VALUES
GO

SELECT 'To_Be_Deleted', * FROM [dbo].[To_Be_Deleted] UNION ALL
SELECT 'To_Be_Truncated', * FROM [dbo].[To_Be_Truncated]
GO

We will also check the current identity value after data is inserted. It’s the same.

SELECT 'To_Be_Deleted',	'IDENT_CURRENT', IDENT_CURRENT('[dbo].[To_Be_Deleted]') UNION ALL
SELECT 'To_Be_Truncated', 'IDENT_CURRENT', IDENT_CURRENT('[dbo].[To_Be_Truncated]')
GO

Now is time to remove data from our tables. There is a difference coming: We will empty the first table via the DELETE command and the second one will be emptied using the TRUNCATE command.

DELETE FROM [dbo].[To_Be_Deleted]
TRUNCATE TABLE [dbo].[To_Be_Truncated]
GO

DBCC CHECKIDENT('[dbo].[To_Be_Deleted]', RESEED, 1)
DBCC CHECKIDENT('[dbo].[To_Be_Truncated]', RESEED, 1)
GO

SELECT 'To_Be_Deleted',	'IDENT_CURRENT', IDENT_CURRENT('[dbo].[To_Be_Deleted]') UNION ALL
SELECT 'To_Be_Truncated', 'IDENT_CURRENT', IDENT_CURRENT('[dbo].[To_Be_Truncated]')
GO

We will check the current identity value now: It’s the same.

Next, we will again insert some data into our tables. Exactly as before.

INSERT INTO [dbo].[To_Be_Deleted] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Deleted] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Deleted] DEFAULT VALUES
GO

INSERT INTO [dbo].[To_Be_Truncated] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Truncated] DEFAULT VALUES
INSERT INTO [dbo].[To_Be_Truncated] DEFAULT VALUES
GO

SELECT 'To_Be_Deleted', * FROM [dbo].[To_Be_Deleted] UNION ALL
SELECT 'To_Be_Truncated', * FROM [dbo].[To_Be_Truncated]
GO

SELECT 'To_Be_Deleted', 'IDENT_CURRENT', IDENT_CURRENT('[dbo].[To_Be_Deleted]') UNION ALL
SELECT 'To_Be_Truncated', 'IDENT_CURRENT', IDENT_CURRENT('[dbo].[To_Be_Truncated]')
GO

If we will check data in both tables, there is the surprise immediately visible:

The table which was emptied using the DELETE command has the first identity value 2 whereas the second table emptied using the TRUNCATE command has the first identity value 1!

This difference is also approved via the IDENT_CURRENT() function.

I know that this behavior isn’t too intuitive at first look. But what is positive (only:) about it is that it’s properly documented at the right place in the DBCC CHECKIDENT() command description:

The current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. If rows are present in the table, or if all rows have been removed by using the DELETE statement, the next row inserted uses new_reseed_value + the current increment value.

To be honest, seniors should know, for sure. But juniors are spending hours investigating this based on my experience. There is a good comparison of DELETE vs TRUNCATE on sqlhack.com, but still, the RESEED issue isn’t clearly explained there.