Have you ever wondered how SQL Server’s identity columns behave when you perform operations like DELETE, TRUNCATE, or RESEED? In this article, we will explore the behavior of identity columns in different scenarios.

Let’s start by creating a temporary table with an identity column that begins with a value of 11:

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

When the seed value is set to 11, the next value that is inserted will have an identity column value of 11.

Now, let’s see the effect of a DELETE statement:

-- Delete Data
DELETE FROM [TestTable]
GO

When the DELETE statement is executed without a WHERE clause, it will delete all the rows. However, when a new record is inserted, the identity value will increase from 11 to 12. It does not reset but keeps on increasing.

Next, let’s explore the effect of a TRUNCATE statement:

-- Truncate table
TRUNCATE TABLE [TestTable]
GO

When the TRUNCATE statement is executed, it will remove all the rows. However, when a new record is inserted, the identity value will be reset to the original seed value of the table, which is 11.

Finally, let’s look at the effect of a RESEED statement:

-- Reseed
DBCC CHECKIDENT ('TestTable', RESEED, 1)
GO

If you notice, we are reseeding the value to 1, even though the original seed value was 11. When we insert one more value and check the identity value, it will generate the new value as 2. The new value is calculated as Reseed Value + Interval Value, which in this case is 1 + 1 = 2.

Now, let’s summarize the behavior of identity columns:

• DELETE: The identity value keeps increasing and does not reset.
• TRUNCATE: The identity value is reset to the original seed value of the table.
• RESEED: The identity value is set to the specified reseed value.

It’s important to understand these behaviors to avoid any confusion or unexpected results when working with identity columns in SQL Server.

That’s all for this article. I hope you found it helpful in understanding how SQL Server’s identity columns behave in different scenarios. If you have any questions or would like to share your thoughts, please leave a comment below.

Happy coding!