SQL SERVER – Answer – Value of Identity Column after TRUNCATE command
Posted
by pinaldave
on SQL Authority
See other posts from SQL Authority
or by pinaldave
Published on Mon, 27 Aug 2012 01:30:41 +0000
Indexed on
2012/08/27
21:44 UTC
Read the original article
Hit count: 398
PostADay
|sql
|SQL Authority
|SQL Query
|SQL Server
|SQL Tips and Tricks
|SQLServer
|T SQL
|Technology
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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology
© SQL Authority or respective owner