SQL SERVER – Answer – Value of Identity Column after TRUNCATE command

Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time. Here is the scenario to set up the puzzle.

• Create Table with Seed Identity = 11
• Insert Value and Check Seed (it will be 11)
• Reseed it to 1
• Insert Value and Check Seed (it will be 2)
• TRUNCATE Table
• Insert Value and Check Seed (it will be 11)

Let us see the T-SQL Script for the same.

USE [TempDB] GO
-- Create Table
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(11,1) NOT NULL,
[var] [nchar](10) NULL
) ON [PRIMARY] GO
-- Build sample data
INSERT INTO [TestTable] VALUES ('val')
GO
-- Select Data
SELECT *
FROM [TestTable] GO
-- Reseed to 1
DBCC CHECKIDENT ('TestTable', RESEED, 1)
GO
-- Build sample data
INSERT INTO [TestTable] VALUES ('val')
GO
-- Select Data
SELECT *
FROM [TestTable] GO
-- Truncate table
TRUNCATE TABLE [TestTable] GO
-- Build sample data
INSERT INTO [TestTable] VALUES ('val')
GO
-- Select Data
SELECT *
FROM [TestTable] GO
-- Question for you Here
-- Clean up
DROP TABLE [TestTable] GO

Now let us see the output of three of the select statements.

1) First Select after create table

2) Second Select after reseed table

3) Third Select after truncate table

The reason is simple: If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

Reference: Pinal Dave (https://blog.sqlauthority.com)