1 of 2

Soft delete to improve performance: all wrong?

A few years back I read soft delete would improve performance of deletion, because instead of actually removing the item and impact the clustered (and all other) indices, it would just change a value.

I recently tried this and I got very disappointing performances.

So I searched through the web for a good implementation of soft deletion and the first things I ended up reading was articles/blogs saying "soft deletion is bad".

Apparently it cause most harm than good when the table gets huge...

I don't think what I did makes table way more huge tables because a nightly (each day) maintenance removes (for real) all rows marked for deletion.

So, are soft deletion really bad for performance? Do real deletion cause a whole clustered table to be fully reordered (which would be a lot of disk operations!!!)?