Have you ever wondered how SQL Server handles identity values in tables? In this blog post, we will explore the concepts of identity values, and the differences between DELETE, TRUNCATE, and RESEED in SQL Server.

Before we dive into the details, let’s set up a scenario to better understand the concepts. Imagine we have a table called TestTable with an identity column named ID. The seed value for this column is set to 11.

First, we insert a value into the table and check the seed value. As expected, the seed value is 11. Next, we reseed the identity column to 1 and insert another value. Now, when we check the seed value, it is incremented to 2.

Now, let’s truncate the table and insert a new value. Surprisingly, when we check the seed value this time, it is reset back to 11. This behavior is because when a table contains an identity column, the counter for that column is reset to the seed value defined for the column.

Here is the T-SQL script for the scenario:

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

-- Clean up
DROP TABLE [TestTable]
GO

By understanding this behavior, we can effectively manage identity values in our SQL Server databases. It is important to note that DELETE and TRUNCATE have different effects on identity values. DELETE retains the identity values, while TRUNCATE resets them.

I hope this blog post has provided you with a clear understanding of SQL Server identity values and the differences between DELETE, TRUNCATE, and RESEED. If you have any further questions or comments, please feel free to leave them below.