Deletion Of Primary Keys [Resolved]

Posted by Kulprachi84 under Sql Server on 3/16/2012 | Points: 10 | Views : 2137 | Status : [Member] | Replies : 11

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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?

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	Moreover identity has some disadvantages, generally faced during Data Replication Refer to these links: http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/ http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/aa11c793-2851-4626-834b-f7ac0b021696/ Thanks, Sanjay Kulprachi84, if this helps please login to Mark As Answer. \| Alert Moderator

Moreover identity has some disadvantages, generally faced during Data Replication
Refer to these links:
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html
http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/aa11c793-2851-4626-834b-f7ac0b021696/

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

0	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

0	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

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

0	@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

0	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

0	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

0	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

0	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

0	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

0	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

Latest Posts