CODEWITHSUNDEEP
sql-serverdb-structure
hgulyan
hgulyan

Reputation: 8249

Store deleted rows in a table

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.

Upvotes: 1

Views: 1817

Answers (4)

Pavel Urbančík
Pavel Urbančík

Reputation: 1496

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.

Upvotes: 1

Kane
Kane

Reputation: 16812

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.

Upvotes: 1

Skrealin
Skrealin

Reputation: 1114

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.

Upvotes: 1

Noon Silk
Noon Silk

Reputation: 55172

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.

Upvotes: 2

Related Questions