SQL SERVER – DELETE, TRUNCATE and RESEED Identity

Yesterday I had a headache answering questions to one of the DBA on the subject of Reseting Identity Values for All Tables. After talking to the DBA I realized that he has no clue about how the identity column behaves when there is DELETE, TRUNCATE or RESEED Identity is used.

Let us run a small T-SQL Script.

Create a temp table with Identity column beginning with value 11. The seed value is 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 seed value is 11 the next value which is inserted has the identity column value as 11.

— Select Data

SELECT *
FROM [TestTable] GO

Solarwinds

SQL SERVER - DELETE, TRUNCATE and RESEED Identity reseed1

Effect of DELETE statement

-- Delete Data
DELETE FROM [TestTable] GO

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

-- Build sample data
INSERT INTO [TestTable] VALUES ('val')
GO
-- Select Data
SELECT *
FROM [TestTable] SQL SERVER - DELETE, TRUNCATE and RESEED Identity reseed2

Effect of 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 is increased from 11 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.

-- Build sample data
INSERT INTO [TestTable] VALUES ('val')
GO
-- Select Data
SELECT *
FROM [TestTable] GO

SQL SERVER - DELETE, TRUNCATE and RESEED Identity reseed1

Effect of RESEED statement

If you notice I am using the reseed value as 1. The original seed value when I created table is 11. However, I am reseeding it with value 1.

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

When we insert the one more value and check the value it will generate the new value as 2. This new value logic is Reseed Value + Interval Value – in this case it will be 1+1 = 2.

-- Build sample data
INSERT INTO [TestTable] VALUES ('val')
GO
-- Select Data
SELECT *
FROM [TestTable] GO

SQL SERVER - DELETE, TRUNCATE and RESEED Identity reseed3

Here is the clean up act.

-- Clean up
DROP TABLE [TestTable] GO

Question for you:

If I reseed value with some random number followed by the truncate command on the table what will be the seed value of the table. (Example, if original seed value is 11 and I reseed the value to 1. If I follow up with truncate table what will be the seed value now?

Here is the complete script together. You can modify it and find the answer to the above question. Please leave a comment with your answer.

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

Solarwinds
Previous Post
SQL SERVER – Reseting Identity Values for All Tables
Next Post
SQLAuthority News – Featuring in Call Me Maybe The Developer Way – Pluralsight Video

Related Posts

No results found

69 Comments. Leave new

  • Hi Pinal,

    Multiple columns and multiple rows, one column is for Serial number (1,2,3,4,……n). The client removes entry no. 95, so the entry no. 96 should get replaced by the no. 95 and so on. Sequence of Serial Numbers should be maintained.

    how to do that ?

    Reply
  • Khndaker Soyeb Md Fazle Rabbee
    November 15, 2015 11:02 am

    I know that THERE IS ABSOLUTELY NO CHANCE OF RESETING THE VALUE BY ITSELF i.e value is again generating from 1.· IF IT HAPPENS, can you please give me a suggestion to solve it?

    Reply
  • delete last record from table. — here example last record is at row 11 , now after deleting row is at 10
    then find max row of that table – here we will get row as 10, because max is now 10
    then reseed value with max–just keep max value in one variable and paas to reseed query..here reseet value will be 10,,and next record will insert from 11
    reinsert last record we have deleted — here last record will be at any row…

    We are done!

    Reply

Leave a Reply

Menu