SQL SERVER – Answer – Value of Identity Column after TRUNCATE command

Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time. Here is the scenario to set up the puzzle.

  • Create Table with Seed Identity = 11
  • Insert Value and Check Seed (it will be 11)
  • Reseed it to 1
  • Insert Value and Check Seed (it will be 2)
  • TRUNCATE Table
  • Insert Value and Check Seed (it will be 11)

Let us see the T-SQL Script for the same.

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
-- Question for you Here
-- Clean up
DROP TABLE [TestTable]
GO

Now let us see the output of three of the select statements.

1) First Select after create table

2) Second Select after reseed table

3) Third Select after truncate table

The reason is simple: If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

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

 

 

 

About these ads

4 thoughts on “SQL SERVER – Answer – Value of Identity Column after TRUNCATE command

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

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

  3. For an identity fieldthat start with 1and increments by 1, how to reset the identity back to 1after identity value = 99
    Here is what I need to do:-
    In my table I have following fields:-Contract_Num, PO, Cont_Exp_Dt, SP_Run_DT , FileNm and I am trying to capture identity value is such a way that identity value increments only when the SP_run_Dt changes, ie-for example if I start to populate my table on 2013-12-12 then the value of sp_Run_Dt will be 2013-12-12 for for all the rows that were inserted on that day. And the identity value should be 1 for all those rows.
    Now in my example case, if I insert some more data on 2014-01-01, then the value of sp_Run_Dt will be 2014-01-01 for for all the rows that were inserted on that day. And the identity value should be 2 for all those rows that were inserted with SP_run_Dt = 2014-01-01 .
    And after the identity value=99 is reached then the identity value should reset to 1.
    Thanks in advance

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