RECREATE AN ID OF A ROW AFTER BEING DELETED

Question

RECREATE AN ID OF A ROW AFTER BEING DELETED

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

Eirikur Eiriksson (1/23/2016)
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:
😎

Hopefully you can read them (or pick them up) sequentially and don't have to jump around.

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

JustMarie SSCertifiable Points: 7771 More actions · Answer 2

The ONLY business reason I can think of for this situation is a legal requirement that there be no gaps in the numbering. There's a vague memory of a country where invoice numbers couldn't have gaps.

Otherwise I'm with the rest of them in not reusing an ID number. Even with impeccable dependency cleanup there's the potential for problems.

These are the situations where asking why is necessary. Unless there's a business justification it's worth taking the time to discuss this and explain all the potential pitfalls.

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

JustMarie (1/25/2016)
The ONLY business reason I can think of for this situation is a legal requirement that there be no gaps in the numbering. There's a vague memory of a country where invoice numbers couldn't have gaps.
Otherwise I'm with the rest of them in not reusing an ID number. Even with impeccable dependency cleanup there's the potential for problems.
These are the situations where asking why is necessary. Unless there's a business justification it's worth taking the time to discuss this and explain all the potential pitfalls.

Not applicable in that situation and even worse, reusing the values would probably breach those requirements as they require the ID to be retained by the imposed record retention period, overwriting the previous would be next to a fraud;-)

😎

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Eirikur Eiriksson (1/25/2016)
JustMarie (1/25/2016)
The ONLY business reason I can think of for this situation is a legal requirement that there be no gaps in the numbering. There's a vague memory of a country where invoice numbers couldn't have gaps.
Otherwise I'm with the rest of them in not reusing an ID number. Even with impeccable dependency cleanup there's the potential for problems.
These are the situations where asking why is necessary. Unless there's a business justification it's worth taking the time to discuss this and explain all the potential pitfalls.
Not applicable in that situation and even worse, reusing the values would probably breach those requirements as they require the ID to be retained by the imposed record retention period, overwriting the previous would be next to a fraud;-)
😎

The only situation I can think of that is when a transaction rolls back and skips an identity value. If absolutely needed, probably a void invoice can be inserted at the end of the day to represent that.

Instead of leaving an identity column, which could be unpredictable, to manage a legal requirement, I'd use a different column that could be controlled more easily.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

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

Your boss may be the simple one to request such a thing. However, to fullfill the request, you can use the OUTPUT clause of the DELETE statement to contain the IDs of deleted rows into a temp table, which can then be used for subsequent insertions.

DECLARE @DeletedIDs TABLE ( ID int NOT NULL PRIMARY KEY );

DELETE MyTable

OUTPUT DELETED.ID

INTO @DeletedIDs;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

Eirikur Eiriksson

SSC Guru

Points: 182911

January 25, 2016 at 12:49 pm

#1854277

Looks like abdellahmoh2016 has left the building

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 7

JustMarie (1/25/2016)
The ONLY business reason I can think of for this situation is a legal requirement that there be no gaps in the numbering. There's a vague memory of a country where invoice numbers couldn't have gaps.

In the very same country (as in any other country with a more or less developed accounting legislation) it's not permitted by law to delete invoices.

Invoices can be "taken back" only by issuing corresponding credit noted.

Deleting an invoice is simply illegal.

_____________
Code for TallyGenerator