Reuse deleted primary key id

Hello,

Can I reuse the deleted primary key id? I'm using SQLServer2005.

cheers,
imperialx

Yeah, that is no problem.

|||

Hi jamesqua,

I should have asked like this, "How can I reuse the deletedprimary key id?"

cheers,
imperialx

|||

Can i no u r exact requirement. deleted primary key id means ?

if its a value no problem. U can reuse it.

I think There is no way to store the deleted value(ofcourse u can place into a temp table or another. Incase of procedures its different).Plz check out

Exact wt i need is u r requirement . How u want to deal it.Explanation needed (Thank u)

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi srijaya,

srijaya_ramaraju@.yahoo.com:

...Exact wt i need is u r requirement . How u want to deal it.Explanation needed (Thank u)...

The primary key is incremented by 1 in every addition of data by default, letsay the primary key value is 56, if this row is deleted then I couldn't use thevalue 56 because the next primary key value will be 57 when adding a new data.

My tablewill now look like so, as you can see the id field values aren't sequentially accumulatedby 1.

id |username
1 | Tedd
2 | Scott
3 | Bing
57 | Mich
58 | Jake
76 | Jenn
77 | Lea

Can SQLServer2005 reuse the deleted primary key for me to have a sequencial value for my id fields?

Hope I explain my problem plainly.

cheers,
imperialx

|||

U r primary key column is defined with Identity property. Thats the reason u r Primary column id will be incremented by 1(or as per the seed). Once we removed that entry. I think its not possible(99.99%). So choice is u rs. U want to go with identity column u can. or manually u have to write the code.

Plz correct me if i m wrong.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

If your primary key is an identity column you can't reuse it. You have to rely on the number generated by sql server and can't put your own values in there. If this behavior does not meet your requirements I would suggest going with a non-identity public key.