Implementing soft delete

I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.

My table structure would be similar to the following:

create table myTable
(
  id int,
  name varchar(50),
  IsDeleted bit,
  DeletedDate datetime
);

The IsDeleted column has a default value of no and the DeletedDate is not populated until the row is actually marked as deleted. I include a date column in the event I ever want to know when a row was soft deleted.

Then when you query your data, you will include this column in the WHERE clause:

select id, name
from myTable
where IsDeleted = 0

Note: You will want to include an index on the IsDeleted column.

The answer given by @bluefeet is good enough to help you.

Soft delete's are common practice .Please have a look at some of the urls that will suggest you the pros and cons of soft deletes and some other great info.

http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea

http://stackoverflow.com/questions/378331/physical-or-logical-delete-of-database-record

There is another approach many have tried : the Tombstone Table approach. There was a post that compare the two techniques:

• Here is the accepted answer
• Here is my answer (where I combined the two approaches)

In that answer, I show how it requires

• properly indexing tables
• redoing all SELECT queries
• marking DELETEs
• performing periodic table maintenance.