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
 Old Forums
 CLOSED - General SQL Server
 Identity property or NOT?

Author  Topic 

Johnyalm
Starting Member

49 Posts

Posted - 2004-10-12 : 06:32:58
I am running a DB where all Primary Keys are set with property "Identity " = YES and "Identity increment" = YES.

We delete records from some of the tables each day, and now the problem of incrementing the PK has come to our minds.

Is there any way to "reset" the identyfield and start all over from 1 again, and at the same time keep al relations (FK:s) intact?

Or, should we NOT use Identity increment when we has a table that are deleted from.

The problem is that some day we will reach tha last number in the PK (the integers will "end" some day :-).

Because we are deleteing, it would be fine with us if the DB resuses PK rows that are not used, and then we will never reach the integer limit!

Thanks in advance for helping me out!

Johny

www.mirrorgate.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-12 : 07:02:52
You can change the seed via dbcc checkident so you can reuse numbers when you are reaching the limit. You can also use negative numbers and a negative step.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-10-12 : 07:04:16
search here for "dbcc checkident"
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-12 : 07:06:14
are you deleting entire table contents. If so, you can use TRUNCATE TABLE. This will reset the identity column. (but is a non logged operation, beware!!!).


Hemanth Gorijala
BI Architect / DBA...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 10:28:51
quote:
Originally posted by hgorijal

are you deleting entire table contents. If so, you can use TRUNCATE TABLE. This will reset the identity column. (but is a non logged operation, beware!!!).


Hemanth Gorijala
BI Architect / DBA...



Not if there's RI they can't





Brett

8-)
Go to Top of Page
   

- Advertisement -