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

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)

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

  • The answer is 11.

    When a reseed is applied before a truncate, the truncate will reset the counter for the column to the (original) seed of the column, not the reseed of the colum as this no longer exists.

    In answer to Debashis, DELETE does not do the same as truncate, it maintains the current seed. Truncate is more efficent than delete but in order to get back to the last seed (as with DELETE) a reseed would be needed after the TRUNCATE.

    https://www.microsoft.com/en-us/download/details.aspx?id=51958

    “The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.”

    https://www.microsoft.com/en-us/download/details.aspx?id=51958

    “TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead.”

    Fun:

    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

    — Reseed
    DBCC CHECKIDENT (‘TestTable’, RESEED, 600)
    GO

    –play here, try with and without ——
    — Truncate table
    TRUNCATE TABLE [TestTable]
    GO
    —–
    — Put sample data in again
    INSERT INTO [TestTable]
    VALUES (‘val’)
    GO

    — Select Data
    SELECT *
    FROM [TestTable]
    GO

    — Clean up
    DROP TABLE [TestTable]
    GO

    Reply
  • Hi Pinal, its really very useful post. but I have a doubt that, suppose I have 100 record in my table and I delete any specific record, then how can I reuse identity value of deleted row when If I insert a new row to the table. please explain…….

    Reply
  • jeevanudanjeevi
    December 18, 2012 1:22 pm

    Its great explanation

    Answer is 11

    CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(11,1) NOT NULL,
    [var] [nchar](10) NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO [TestTable]
    VALUES (‘val’)

    SELECT *
    FROM [TestTable]
    —————————————————————————————–
    DBCC CHECKIDENT (‘TestTable’, RESEED, 1)

    INSERT INTO [TestTable]
    VALUES (‘val’)

    SELECT *
    FROM [TestTable]
    —————————————————————————————–

    TRUNCATE TABLE [TestTable]

    INSERT INTO [TestTable]
    VALUES (‘val’)

    SELECT *
    FROM [TestTable]

    —————————————————————————————–

    Reply
  • i am inserting records using select statement but insertion fails how i can find out which column cause failure in sql

    Reply
    • Hi Deepak,

      Try to check first the constraints of the columns in your table, then, move forward. or you might want to DROP and CREATE a new table to test..

      Regards,
      Ruther Roque

      Reply
  • how can we rollback “DELETE” command without using transaction?
    Please help .

    Reply
  • great sir

    Reply
  • I got different results … after truncate it return next identity as 11 and after Delete it give new identity as 2 ……

    Reply
  • Thank you! Very clear and useful, and I appreciate the time you take to share your knowledge!

    Reply
  • I need one SQL QUERY.

    In my table i am having one column named ID(non Primary Key) which contains the values like 1,2,3,4,5 etc. If i am trying to insert 4 again that column means then new 4 should insert and old 4 should replace by 5, old 5 sholud be replaced by 6 like this orderly.

    Can any one please provide the sql server query.

    Reply
  • Can you help me code SQL , I have 2 table with data the seem, but when I copy data from FolioTransaction to BKFolioTransaction the system error:

    Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table ‘SMILE_FO.dbo.BKFolioTransaction’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

    I know 2 table have [TransactionID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, I can’t not copy :(

    Can you help me???

    Your code:

    SET IDENTITY_INSERT dbo.BKFolioTransaction ON;

    INSERT INTO SMILE_FO.dbo.BKFolioTransaction
    select * from SMILE_FO.dbo.FolioTransaction — where RefNumber =@Checkno

    SET IDENTITY_INSERT dbo.BKFolioTransaction off;

    Reply
  • I tested and the result is 1.

    Reply
  • value should be 1 after reseed.When We insert record then ID column starts with 2

    Reply
  • Gaurav Srivastava
    July 19, 2014 9:21 am

    After TRUNCATE TABLE Seed value will be initial seed value in this case it will be 11 not that reseed random number value.
    because Truncate drop and recreate table again.

    Reply
  • A post at the following link suggests that “TRUNCATE TABLE would be preferred as it does a more efficient DELETE and resets the IDENTITY seed at the same time.” and you state “TRUNCATE resets the identity value to the original seed value of the table”.

    So I guess there is no conflict if “the original seed value of the table” was 0; correct?

    Reply
  • I want a “Pinal Dave” tee shirt! Large, black!

    Reply
  • Hi,
    Please help me,
    someone is truncate table from my DB so i want to know who perform this operation.

    Reply
  • Hi Guys,
    someone by mistake truncate table from my DB then how to know who perform this command.

    Reply
  • yes ,originally the identity value we gave while creating the table will be in charge….

    Reply
  • What if we have contraints in our table and we would like to Delete data and reseed Identity .

    Reply
  • www.facebook.com
    May 31, 2015 9:29 am

    I am regular reader, how are youu everybody?

    This piece of writing posted at this website is genuinely nice.

    Reply

Leave a Reply

Menu