Logical Delete

Using the 'DELETE' statement erases the row definitely and there is no way to restore the rows. That’s what we call Physical Delete. But here is a faster way - to remark the rows as deleted rows. This is calling Logical Delete or Application Delete.

Using the 'DELETE' statement erases the row definitely and there is no way to restore the rows. That’s what we call Physical Delete. But here is a faster way - to remark the rows as deleted rows. This is calling Logical Delete or Application Delete.

Three options are available how to implement it:

Option 1: Multiple the RowID value by 10, or when it is string add a char, and using that digit for sign the row status.

The big advantage here, is that the rows order doesn’t change after multi by –1, so it saves page split and defrag index. For example: 50, 60, 70 -> 50, 61, 70

Option 2: Multiple the RowID value by -1 and using the sign (positive & negative). When it is string add the minus char.

The multiple by –1 effects on the sort order and the row is moved. Here the advantage is that all the deleted rows are concentrate into closing pages.

For example: 5, 6, 7, 8 -> -8, -6, 5, 7

All the negative values move to the beginning of the index

Option 3: Add an extra column to the table. Here the RowID doesn’t change it value at all.

At options 1 and 2 the PK value change !? ooohp ….

The entity ID value does not change actually, only the RowID as a mathematical value, which the database holds. It is a math trick to avoid adding an extra column. If someone afraid to update the PK, you can use option 3.

What about FK that points to PK !?

That’s right. It is impossible to update the key when it is pointed by its child table/s. If you wish, implements the FK by your own code.

Any attempting to change the key raises the following error: “UPDATE statement conflicted with COLUMN REFERENCE constraint”

You can define the FK with “ON UPDATE CASCADE” then all of the foreign key values are also updated to the new value specified for the key.

I recommend to define two views: one for the deleted rows and one for the table-rows.

Advantage

· Due to Logical Delete you can imitate the Identity() function's property, which knows the max RowID that was created since the table was created without rows existing.

· Does not lost data

· It is possible to un-delete rows

· Easy to debug, to re-delete and run the process again

· Easy to sync the deleted rows after dis-connection event or replication failure.

· Moving data into history tables can be done by an off-line process and not be part of the real time transaction.

· Avoid fragmentation of tables and indexes, because new rows located into new pages and not exchange location with the old deleted rows.

· The logical delete, is actually an update operation on the key, requires fewer locks then physical delete. Logical Delete locks the key only when physical delete locks all indexes and the table.

· Physical Delete act all FK point to the table, when Logical Delete doesn't act the constrain, better performance

A table with Logical Delete mechanism is growing up, therefore a maintain process is mandatory here.

Summary

We saw here few prominent features which effect the DB design. The Logical Delete code is a deep part of the database model, therefore, the designer might decide advance about it.