SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Identity property or NOT?		  Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnyalm
Starting Member

49 Posts
Posted - 10/12/2004 :  06:32:58  Show Profile  Visit Johnyalm's Homepage
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
Edited by - Johnyalm on 10/12/2004 06:33:50

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 10/12/2004 :  07:02:52  Show Profile  Visit nr's Homepage
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
Flowing Fount of Yak Knowledge

Ireland
2915 Posts
Posted - 10/12/2004 :  07:04:16  Show Profile
search here for "dbcc checkident"
Go to Top of Page

hgorijal
Constraint Violating Yak Guru

India
277 Posts
Posted - 10/12/2004 :  07:06:14  Show Profile
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 - 10/12/2004 :  10:28:51  Show Profile
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000