Store deleted rows in a table

Question

Is it a good practice to have an is_deleted column in a SQL Server table or there's another way of dealing with this question?

Another solution I can think of is to delete that row and inserting it to another table for deleted rows.

What's the best solutions?

Thanks in advance.

Why the downvote without any explanation? – hgulyan Nov 06 '10 at 19:59 — hgulyan, Nov 06 '10 at 19:59
+1 corrected downvoter. This is reasonable question. – Igor Borisenko Oct 05 '11 at 07:42 — Igor Borisenko, Oct 05 '11 at 07:42

score 2 · Accepted Answer · answered Nov 04 '10 at 06:23

2

Depends.

I'd go with the IsDeleted field, and then every-now-and-then run an archive process that moves these rows off to an archived table, such that they don't take up room in commonly-queried tables, but can still be retrieved when required.

answered Nov 04 '10 at 06:23

Noon Silk

54,084
6
88
105

Depends on what kind of conditions? – hgulyan Nov 04 '10 at 06:25
2

You keep the flag if you don't want to delete the rows. Sometimes you want to refer back to them, sometimes you want to show some kind of a history or audit trails things similar to version control systems. Usually its advised not to delete rows from the table as once they are deleted you can't get them back. Archiving is a good solution when the original table gets large in size and it starts causing performance issues. – Faisal Feroz Nov 04 '10 at 06:28

score 1 · Answer 2 · answered Nov 04 '10 at 08:47

I'd say use the IsDeleted flag rather than deleting the rows (or alternatively IsEnabled, which I use for stuff like User tables). Storage is cheap and just because the row is no longer valid doesn't mean the data is worthless.

Another thing I usually have is a RowCreatedDateStamp column, its been useful many times.

score 1 · Answer 3 · answered Nov 04 '10 at 08:59

1

Might be a little late in answering your question, but you should really read Udi Dahan's post in using "soft deletes" like the IsDeleted column.

answered Nov 04 '10 at 08:59

Kane

16,471
11
61
86

score 1 · Answer 4 · answered Nov 04 '10 at 12:23

It really depends on your scenario. Marking record as deleted is way less I/O heavy than real deletes (no constraint checking, no page coalescing, etc.), but it also creates lots of additional complexity in your code, because now your application needs to cope with soft-deleted records everywhere. All queries must take this into account, because some might want to return even deleted records, some strictly can't, etc.

Another important thing to note is that your application might require hard deletes because of legislation or customer requirements.

Store deleted rows in a table

4 Answers4

Linked