How to reset IDENTITY Column in a Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to reset IDENTITY Column in a Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-22 : 08:00:53

Kishore writes "In a table created an Identity column, after reaching the max. value, now I want to start from 1 onwards. Is it possible ?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-22 : 08:02:05

You can reset the identity value, but it cannot be reset lower than the maximum value that exists in the table. And since you probably have a primary key or unique constraint on that column, you can't change it to 1 if 1 is already in the table.

Look up DBCC CHECKIDENT in Books Online.

scottpt
Posting Yak Master

186 Posts

Posted - 2005-04-22 : 11:10:53

You can also move the table then truncate the original table then reinsert.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 11:41:12

>>but it cannot be reset lower than the maximum value that exists in the table

Actually, it can. Sql just isn't smart enough to skip existing values and will generate the appropriate error when the new (generated) identity already exists in the table.

So, probably what Rob was saying is that if your intention is to let sql "fill in" missing values by reseeding the identity, it won't work.


create table #temp1 (A int, rowid int identity(1000000,1))
GO
insert #temp1 (A) select 1 union select 2 union select 3
select * from #temp1
GO
dbcc checkident ('#temp1', reseed, 0)
GO
insert #temp1 (A) select 1 union select 2 union select 3
select * from #temp1

GO
drop table #temp1

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-22 : 16:18:26

If the table has an integer identity column you might be able to get away with reseeding it to a really low negative number:

dbcc checkident ('#temp1', reseed, -2147483600 )

It would take a while before it starts catching up to your current data, and maybe you have a purge process in place so the old data would be removed by then.

Of course, it's up to you to make sure this will not cause any problem with the logic of your application.

Long term, you should consider changing the datatype to somthing big enough to hold your data, like BIGINT, without having to go through this. You would have to change the datatypes in related tables too, so that may be a lot of work.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources