RECREATE AN ID OF A ROW AFTER BEING DELETED

Question

RECREATE AN ID OF A ROW AFTER BEING DELETED

abdellahmoh2016

Grasshopper

Points: 14
More actions
January 20, 2016 at 2:19 pm

#315130

HI ALL
I'm new in T-SQL so i need your help
My problem is : After deleting a row , is there a way to retrieve the ID of that row ?
Because i want to assign this ID to another customer in my customer's table
Thanks

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

abdellahmoh2016 (1/20/2016)
HI ALL
I'm new in T-SQL so i need your help
My problem is : After deleting a row , is there a way to retrieve the ID of that row ?
Because i want to assign this ID to another customer in my customer's table
Thanks

Quick suggestion, use the output clause with the delete statement to insert the ID value into a table of "available" Ids.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

My question would be 'why'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

GilaMonster (1/20/2016)
My question would be 'why'?

+1000

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

GilaMonster (1/20/2016)
My question would be 'why'?

+1

😎

Didn't want to open that Pandora's box

abdellahmoh2016 Grasshopper Points: 14 More actions · Answer 5

Hi Thank you for your reply

It's because my boss suggested me simple like that

LinksUp SSCertifiable Points: 6526 More actions · Answer 6

abdellahmoh2016 (1/22/2016)
Hi Thank you for your reply
It's because my boss suggested me simple like that

Quite frankly, your boss does not know what he/she is talking about. Reuse of ID's like this simply a bad practice. What about historical records that have this ID? What about child tables that use this ID? If this is an Identity column, well, you are just asking for more pain.

I see a lot of potential for either deadlocking (multiple concurrent connections trying to do an "insert") or just plain performance overhead by first having to check if there is an available empty row first, then insert later. You have just increased the complexity of your application significantly, for what, sequential numbers? Do yourself and the people who will inherit this application a huge favor and forget about this. Furthermore, if you use foreign keys, this could really create some horribly inconsistent data.

Good luck.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

abdellahmoh2016 (1/22/2016)
It's because my boss suggested me simple like that

Then I would suggest that you suggest to your boss that this is a bad idea, overly complicated, makes auditing hell and has no real benefit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

abdellahmoh2016 (1/22/2016)
Hi Thank you for your reply
It's because my boss suggested me simple like that

Tell your boss to stick to defining functionality, and leave the implementation to those who are paid to know technical details - i.e. you

Or if he/she thinks he/she knows better, give them your chair and tell them to do it themself.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Piling on, I have to agree with the majority opinion here - this is a bad idea. If you're going to reuse IDs that are primary keys, this will lead to more problems later in life. Like Gail said, if you're auditing, your life is going to become much more complicated.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

Ed Wagner (1/23/2016)
Piling on, I have to agree with the majority opinion here - this is a bad idea. If you're going to reuse IDs that are primary keys, this will lead to more problems later in life. Like Gail said, if you're auditing, your life is going to become much more complicated.

Well we can probably all picture a desk with five perfectly sharpened pencils, one for each day of the week, aligned perfectly 90 degr. from the upper edge, perfect untouched notepad positioned 1.75 inches from the pencils..... and an unbroken sequence of customer ID's in the database....

😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Eirikur Eiriksson (1/23/2016)
Ed Wagner (1/23/2016)
Piling on, I have to agree with the majority opinion here - this is a bad idea. If you're going to reuse IDs that are primary keys, this will lead to more problems later in life. Like Gail said, if you're auditing, your life is going to become much more complicated.
Well we can probably all picture a desk with five perfectly sharpened pencils, one for each day of the week, aligned perfectly 90 degr. from the upper edge, perfect untouched notepad positioned 1.75 inches from the pencils..... and an unbroken sequence of customer ID's in the database....
😎

Absolutely. But in what order would the pencils be placed on the table? In what order would you be required to pick them up. 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 12

Ed Wagner (1/23/2016)
Eirikur Eiriksson (1/23/2016)
Ed Wagner (1/23/2016)
Piling on, I have to agree with the majority opinion here - this is a bad idea. If you're going to reuse IDs that are primary keys, this will lead to more problems later in life. Like Gail said, if you're auditing, your life is going to become much more complicated.
Well we can probably all picture a desk with five perfectly sharpened pencils, one for each day of the week, aligned perfectly 90 degr. from the upper edge, perfect untouched notepad positioned 1.75 inches from the pencils..... and an unbroken sequence of customer ID's in the database....
😎
Absolutely. But in what order would the pencils be placed on the table? In what order would you be required to pick them up. 😛

Elementary Ed, one after the other...

😎

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

Eirikur Eiriksson (1/23/2016)
Ed Wagner (1/23/2016)
Eirikur Eiriksson (1/23/2016)
Ed Wagner (1/23/2016)
Piling on, I have to agree with the majority opinion here - this is a bad idea. If you're going to reuse IDs that are primary keys, this will lead to more problems later in life. Like Gail said, if you're auditing, your life is going to become much more complicated.
Well we can probably all picture a desk with five perfectly sharpened pencils, one for each day of the week, aligned perfectly 90 degr. from the upper edge, perfect untouched notepad positioned 1.75 inches from the pencils..... and an unbroken sequence of customer ID's in the database....
😎
Absolutely. But in what order would the pencils be placed on the table? In what order would you be required to pick them up. 😛
Elementary Ed, one after the other...
😎

Wouldn't that by PBAP?

(Pencil By Agonizing Pencil)

I'd pick them up all at once, in one single set-babsed sweep.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 14

Hugo Kornelis (1/23/2016)
Eirikur Eiriksson (1/23/2016)
Ed Wagner (1/23/2016)
Eirikur Eiriksson (1/23/2016)
Ed Wagner (1/23/2016)
Piling on, I have to agree with the majority opinion here - this is a bad idea. If you're going to reuse IDs that are primary keys, this will lead to more problems later in life. Like Gail said, if you're auditing, your life is going to become much more complicated.
Well we can probably all picture a desk with five perfectly sharpened pencils, one for each day of the week, aligned perfectly 90 degr. from the upper edge, perfect untouched notepad positioned 1.75 inches from the pencils..... and an unbroken sequence of customer ID's in the database....
😎
Absolutely. But in what order would the pencils be placed on the table? In what order would you be required to pick them up. 😛
Elementary Ed, one after the other...
😎
Wouldn't that by PBAP?
(Pencil By Agonizing Pencil)
I'd pick them up all at once, in one single set-babsed sweep.

And glue them on each finger for asynchronous writing...:w00t:

😎