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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.