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.
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!Johnywww.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. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-10-12 : 07:04:16
|
search here for "dbcc checkident" |
|
|
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 GorijalaBI Architect / DBA... |
|
|
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 GorijalaBI Architect / DBA...
Not if there's RI they can'tBrett8-) |
|
|
|
|
|