Deletion Of Primary Keys [Resolved]

Posted by Kulprachi84 under Sql Server on 3/16/2012 | Points: 10 | Views : 1163 | Status : [Member] | Replies : 11
Hi,
I have created one table which has primary key. It gets auto incremented when we add new record.
Suppose records are as follows
ID Name
1 ABC
2 PQR
3 XYZ
and I delete 3rd record from it.
Next time while adding new row , primary key will be 4 even if 3rd record is not present. I want to have it 3 instead of 4.
Primary key is unique and not null,but this soft deletion of records from table should be avoided.
How to achieve this?




Responses

Posted by: Hmanjarawala on: 3/16/2012 [Member] Bronze | Points: 50

Up
0
Down

Resolved
Hi Prachi,

best option for this is instead of rely on autogenerated identity key, generate primary key at your own

for that write following Query:

Select @id = IsNull(Max(ID), 0) + 1 From TableName (WithLock)



MArk this as answer, if it helps you........

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/16/2012 [Member] Silver | Points: 50
Posted by: Sksamantaray on: 3/16/2012 [Member] Silver | Points: 25

Up
0
Down
For Autoincrement do you use identity?

Thanks,
Sanjay

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/16/2012 [Member] Silver | Points: 25

Up
0
Down
Hi Prachi,
When database table size is healthy then depending on a aggregate function will hamper the performance.
So its better to keep the last Record no in a table and while you go for inserting a new record use that value+1 .
Also update the old value with new one.
The advantage here is , it searches the value in 1 record only.

Thanks,
Sanjay

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kulprachi84 on: 3/16/2012 [Member] Starter | Points: 25

Up
0
Down
@Sksamantaray : Yes for autoincrement, identity is used.

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/16/2012 [Member] Silver | Points: 25

Up
0
Down
I will suggest not to go for identity for auto-incrementation rather you can follow any one point mentioned above by me and Himanshu

Thanks,
Sanjay

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 3/16/2012 [Member] Starter | Points: 25

Up
0
Down
HI

Is Manual increment gives better performance than Auto increment ?

Thanks

R D Patel

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/16/2012 [Member] Silver | Points: 25

Up
0
Down
Hi,
of-course, because calculation is not required

Thanks,
Sanjay

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 3/16/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks


R D Patel

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kulprachi84 on: 3/16/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks for all answers

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
SET IDENTITY_INSERT ON TABLENAME

SET IDENTITY_INSERT OFF TABLENAME

ALTER TABLE <TABLENAME> DROP CONSTRAINT <CONSTRINTNAME>

REGARDS

sriram

Kulprachi84, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response