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

Solarwinds

SQL SERVER - Answer - Value of Identity Column after TRUNCATE command identityimage1

2) Second Select after reseed table

SQL SERVER - Answer - Value of Identity Column after TRUNCATE command identityimage2

3) Third Select after truncate table

SQL SERVER - Answer - Value of Identity Column after TRUNCATE command identityimage3

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

 

 

 

Solarwinds
Previous Post
SQL SERVER – Download PSSDIAG Data Collection Utility
Next Post
SQL SERVER – A Brief Note on SET TEXTSIZE

Related Posts

No results found

2 Comments. Leave new

  • Is there any method by which i can get to know which all columns of a table are unique using script??

    Reply
  • 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

    Reply

Leave a Reply

Menu