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 (https://blog.sqlauthority.com)
69 Comments. Leave new
It is a nice post.
your question’s ans: After truncate table, seed value is 11.
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
Zero
One is the right answer :)
11 the original seed will remain unchanged even we run the truncate table.
Hello Pinal Sir,
Very good Post. Today I came to know Actual Difference.
After Truncate Commad RESEED wil not work, It will be take the Old IDENTITY value
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.
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.
The new seed value will be 11
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.
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.
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
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 :)
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
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!
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
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…
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
its 11…………..
Truncate statement always sets the seed value to its original value.. In this case its 11
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