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

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]

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

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

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 (http://blog.sqlauthority.com)

About these ads

60 thoughts on “SQL SERVER – DELETE, TRUNCATE and RESEED Identity

  1. The seed value will be the defined seed value of when the table was created.

    So if a table is defined with an identity (1298,1) column, then after a truncate the seed value will be 1298.

    Like

  2. If we will truncate table after DBCC command then again it starts with actual value that u defined during create table.

    Might be reason is when we apply DBCC >> Insert >> select >> truncate >> insert record >> then it again refers to it’s table structure.

    I am not sure i am right or wrong.:)

    Like

  3. Truncate command will reset back to the original seed value, that is 11.

    DBCC CHECKIDENT (‘TestTable’, RESEED, 1) will set a new current identity value for the identity column not the seed value.

    Like

  4. Reseed will change the current identity value to a specific value. It does not change the original value as some might expect. You can search for TRUNCATE and DBCC CHECKINDENT in the helpfiles. On each page there is a small sentence which together give the explanation for the answer to your question.
    Very simple, but valuable lesson. Thanx.

    Like

  5. Though you reseeded with an value, the truncate command will reseed the identity value again to the seed value which was given at the time of table declaration

    Like

  6. Nice posts on yesterday and today regards identity Values. Today question is really good to learn table behaviour. I checked with the scripts to find the answer.

    Creating a table with Identity is 11. After Reseed the table identity value is 1 then Truncate the records from existing table. It maintains the Identity value at the time of table creation and left the reseed value for this table.

    Like

  7. I have found that identity seed value is ‘1’ because of Truncate in last step, below is the single sql script to create table and all the steps and then table clean up.

    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
    DBCC CHECKIDENT (‘TestTable’, RESEED, 1)
    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

    Like

  8. Example, if original seed value is 11 and I reseed the value to 1. If I follow up with truncate table, the next inserted data has ID 11.
    I thought, the next inserted data will have ID 1.
    I think truncate also removes the reseed set.

    Like

  9. Hi Pinal,

    Thanks for this article really it good. It clears my some doubts.

    Answer of your question is : If we call Truncate after Reseed command then the Identity will be changed to initial value means the Identity reset to 11.

    Like

  10. Hi Pinal,

    Thanks a lot for this post.

    Answer: After Truncate command DBCC RESEED will not work, so the reseed value would be 11 i.e. original value instead of 1.which has been reseed by DBCC RESEED Command.

    Like

  11. Reseeding is very dangerous indeed.
    If you keep inserting new values, you’ll end up with duplicate entries because SQL server will not check if the value already exists. And if you add a unique constraint (which would be logical for a identity column) the insert will fail.
    So ‘Truncate Table’ is indeed the best solution to avoid collisions.

    Like

  12. when we used TRANCATE command it will deletes all record of the table and sets identity column’s seed to original seed value whereas RESEED resets identity column’s seed to new seed i.e 1+Random Number

    Like

  13. HI Pinal Sir,
    Answer is Original Seed Value.
    Say…
    if original seed value is 11 and I reseed the value to 1. If I follow up with truncate table, Then the seed value now is 11.

    Thank for Knowledge Sharing :)

    Like

  14. seed value is 11
    b’cas when you can change seed value and insert any value in table,
    it starts with new seed value but when we can use truncate command on table it takes original seed value.

    thanks, it’s very good blog

    Like

  15. Pingback: SQL SERVER – Answer – Value of Identity Column after TRUNCATE command « SQL Server Journey with SQL Authority

  16. The truncate statement will reset the table to its original “seed” value. this would be “11” then.
    Reseeding is not good for the table. It can (will) come to collisions on inserted values since there there is no check on table level and the insert will fail when you insert the value “11” again after reseeding with “2”.

    Great Blog, keep up the good work!

    Like

  17. CREATE TABLE #RESEED(ID INT IDENTITY(11,1), NAME VARCHAR(20))

    INSERT INTO #RESEED (NAME) VALUES (‘A’)

    SELECT * FROM #RESEED

    TRUNCATE TABLE #RESEED

    DBCC CHECKIDENT(‘#RESEED’, RESEED,1)

    INSERT INTO #RESEED (NAME) VALUES (‘A’)

    SELECT * FROM #RESEED

    The Seed value should be : 1

    Like

  18. I want to know one thing..The truncate statement will reset the table to its original “seed” value. this would be “11″ then.
    If we are using delete to delete 5 records and after deleting the records, we are committing the same..then if we want to insert new data then the seed value will not start from 6..it will start from 11..Why is it so? As delete is followed by commit so it should act as truncate and delete the records and seed value permanently and we should able to insert data with deleted seed values..Can anybody put an idea on this?..I am quite new to database…

    Like

  19. i have 3 tables with key relations

    create table dbo.sale
    1,’anday’,61,1000
    2,’rachod’,34,2000
    5,’chris’,24,3000
    7,’corms’,41,4000
    8,’ross’,57,5000
    9,’joe’,38,6000

    create table dbo.client
    4,’pizza’,’holland’,’f&b’
    5,’domino’,’chicago’,’f&b’
    7,’pizzacornar’,’newyork’,’f&b’
    9,’papa johns’,’miching’,’f&b’

    create table dbo.orders
    10,’8/2/96′,4,2,540
    20,’1/30/99′,4,8,1800
    30,’7/14/95′,9,1,460
    40,’1/29/98′,7,2,2400
    50,’2/3/98′,6,7,600
    60,’3/2/98′,6,7,720
    70,’5/6/98′,9,7,150
    60,’3/2/98′,6,7,720

    please answer for this questions.

    1. names of all saleperson who have an order with dominos
    2. the names all saleperson who do not have any order with Dominos& papa johns
    3. the names of sales person that have 2 or more orders worth $150
    4. write sql trigger to insert rows into a table called higher
    achiever (names,age)
    when a sale person sales amount gooes abouve $500

    5. write queries to delete any duplicate rows the orders table

    6. names of the saleperson who have not made any sales or sales less
    then $50 in each year

    explain it

    Like

  20. Hi, Debashis. I did same and getting Seed 11.
    I am using SQL SERVER 2008 and used below queries to delete and commit trans:
    Begin Tran
    delete TestTable
    Commit

    Like

  21. 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.

    http://msdn.microsoft.com/en-us/library/aa258817(v=sql.80).aspx

    “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.”

    http://msdn.microsoft.com/en-us/library/aa260621(v=sql.80).aspx

    “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

    Like

  22. 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…….

    Like

  23. 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]

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

    Like

  24. Pingback: SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051 | SQL Server Journey with SQL Authority

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

    Like

  26. 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.

    Like

  27. 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;

    Like

  28. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  29. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  30. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s