DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.
DBCC CHECKIDENT (yourtable, reseed, 34)
If table has to start with an identity of 1 with the next insert then the table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.
Reference : Pinal Dave (https://blog.sqlauthority.com)
85 Comments. Leave new
Cannot truncate table ‘ TABLE_NAME ‘ because it is being referenced by a FOREIGN KEY constraint.
I have been dealing with the issues around DBCC CHECKIDENT and i think I have found the issue and the solution. I’ve been trying to create an environment where I can refresh the database after doing some development leaving all tables empty. I tried Ron Moses solution but still found some tables would add 1 to the reseed values while others would begin with the next higher value. It turns ou that this is related to whether we are truncating or deleting records due to the presence of a foreign key. I found that the following code solves the problem. Please note that I am refreshing tables in all schemas except dbo.
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
— USE DELETE on Tables with foreign keys and Truncate on others
EXEC sp_MSForEachTable ‘declare @schema varchar(255);
SET @schema = PARSENAME(“?”,2) IF @schema ”dbo”
BEGIN
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
BEGIN
DELETE FROM ?
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED,0) WITH NO_INFOMSGS
END
else
BEGIN
TRUNCATE TABLE ?
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED,1) WITH NO_INFOMSGS
END
END;
‘
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
The != appears to be missing in the code posted
SET @schema = PARSENAME(“?”,2) IF @schema ”dbo”
IF @schema != ”dbo”
DBCC CHECKIDENT(‘schema.TableName’,reseed,34)
Thank u Dave… Please tell me how insert DateDiff values to a table
Date to be compared retrieve from another table..
I mean Start Date taken from tb1, End Date taken from tb2, values inserted to tb2
Thanks a lot.
Greetings from México.
hi
Hi Pinal,
There are 100s of records in a table having identy columns out that 15 records has been deleted. My question is that how to find that what are the identities which has been deleted.
You need to join with the numbers table to find this
select n.number from numbers as n left join your_table as t on n.number=t.id_col
where t.id_col is null
Love your blog Pinal Dave, I use it a lot.
Is it worth mentioning that if the table has no rows then the first row will have an identity of the seed value, not seed + 1?
Hello. This blog is helping me in any manner it can. Am glad using this blog.
I have a question.
Scenario:
I have a table ‘Table1’ with 2 columns namely ‘ID’ & ‘Name’. Primary key on column ‘ID’.
I have 10 records with ids ranging from 1-10.
Suppose I have deleted rows whose ids are 2,5.
I want to refresh my table ‘Table1’ in such a manner that rows whose ids are 3 & 6 should take the place of 2 & 5 respectively and so on.
Is this possible.
Thanks in advance.
Hi, Anas
follow steps given below
(1) remove identity from column ID. (set identity = no)
(2) add ID2 column with identity yes.
(3) update table set ID = ID2;
(4) remove ID2 column
(5) set Identity on column ID
I know this an older article but I’m hoping someone can still assist me.
I have been ‘gifted’ an archive table where someone has started a new seed instead of using the original seed from when the record was in the production table.
I want to reseed 7.8 million records so I can match the last archive record up to the first production record, then turn the seed off in the archive table. My process going forward would then be just moving the record with its original seed to the archive table.
My question is, how can I update the archive seed without blowing up my transaction logs?
Thanks for any suggestions you might have.
i’ve tried all the options that all u people specified,but still it showing error
DBCC CHECKINDENT(tblItem, RESEED,0)
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
Hello Herby,
It’s CHECKIDENT , not CHECKINDENT as you say, hence the error.
There’s no such command as CHECKINDENT on MS-SQL.
Chris.
Hello Herby,
It’s CHECKIDENT , not CHECKINDENT as you say, hence the error.
There’s no such command as CHECKINDENT on MS-SQL.
Chris.
Please follow this code for specific database and schema .
DBCC CHECKIDENT(‘[Database Name].[schema ].[Table Name]’, RESEED, 0)
Hi pinal,
CREATE TABLE dbo.Test(id INT IDENTITY(1,1),Num INT)
GO
INSERT INTO dbo.Test VALUES(1),(2)
GO
DELETE FROM dbo.Test
GO
DBCC CHECKIDENT (‘dbo.Test’,RESEED,10)
GO
INSERT INTO dbo.Test VALUES(1)
GO
SELECT MAX(id) FROM dbo.Test
GO
DROP TABLE dbo.Test
GO
CREATE TABLE dbo.Test(id INT IDENTITY(1,1),Num INT)
GO
DELETE FROM dbo.Test
GO
DBCC CHECKIDENT (‘dbo.Test’,RESEED,10)
GO
INSERT INTO dbo.Test VALUES(1)
GO
SELECT MAX(id) FROM dbo.Test
GO
did you try to predict the output, the result will astonish beware of using Dbcc Checkident
TRUNCATE TABLE will reset properly to 0
Many thanks !
Thanks @Francisco
can u explain pinal
I can … only if I know what do you want me to explain?
Good Day Dave,
I have a dbo.dept table and it currently has 12190 rows. I read your blog and see I can run DBCC CHECKIDENT (yourtable, reseed, 34). My question would be what to insert in the place of 34 and will I keep the existing data ?
Thank You
if you reseed, same identity value would be generated again.
set nocount on
go
create database foo
go
use foo
go
create table identity_table( i int identity(1,1), j varchar(10))
go
insert into identity_table (j) values (‘v1’)
go
insert into identity_table (j) values (‘v2’)
go
insert into identity_table (j) values (‘v3’)
go
insert into identity_table (j) values (‘v4’)
go
insert into identity_table (j) values (‘v5’)
go
Select * from identity_table
delete from identity_table where i in (2,3)
go
Select * from identity_table
go
DBCC CHECKIDENT (identity_table, reseed, 1)
/*
Checking identity information: current identity value ‘5’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
insert into identity_table (j) values (‘v6’)
go
Select * from identity_table
insert into identity_table (j) values (‘v7’)
go
insert into identity_table (j) values (‘v8’)
go
Select * from identity_table