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