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
The seed value will be the defined seed value of when the table was created.
So if a table is defined with an identity (1298,1) column, then after a truncate the seed value will be 1298.
Wouldn’t the seed value be 11? The truncate command would reset the identity to the original identity value of the table.
Wouldn’t the seed value be 11? The truncate command would reset the seed value to the original seed value of the table.
11
If we will truncate table after DBCC command then again it starts with actual value that u defined during create table.
Might be reason is when we apply DBCC >> Insert >> select >> truncate >> insert record >> then it again refers to it’s table structure.
I am not sure i am right or wrong.:)
It goes back to 11 . Now thats a interesting behaviour. I thought it would have being 1.
Hi Pinal,
Reseed Identity value would be set to original seed of the table.
Answer will be 1 ,means Reseed will seed again from 1 after the truncate command passed over the table
seed value will be 11
It will be 11 .
Seed value will be 11(the original seed value)
Truncate command will reset back to the original seed value, that is 11.
DBCC CHECKIDENT (‘TestTable’, RESEED, 1) will set a new current identity value for the identity column not the seed value.
A very interesting explanation to a generally confusing concept. The explanation is very easy to understand and remember.
The seed value will be what it was during table creation i.e. 11.
Reseed will change the current identity value to a specific value. It does not change the original value as some might expect. You can search for TRUNCATE and DBCC CHECKINDENT in the helpfiles. On each page there is a small sentence which together give the explanation for the answer to your question.
Very simple, but valuable lesson. Thanx.
11
Though you reseeded with an value, the truncate command will reseed the identity value again to the seed value which was given at the time of table declaration
Nice posts on yesterday and today regards identity Values. Today question is really good to learn table behaviour. I checked with the scripts to find the answer.
Creating a table with Identity is 11. After Reseed the table identity value is 1 then Truncate the records from existing table. It maintains the Identity value at the time of table creation and left the reseed value for this table.
Thanks for the article, Truncate command will reset the seed value to original seed value
After truncating table the identity will generate from original seed value i.e. from 11.