December 6, 2006 at 12:22 pm
Please advise. Our development group wants to add a logical delete column (bit NOT NULL default 0)to every table. The reason is to make it easier to un-delete data that our customers mistakenly delete. I personally believe backups are to be used for this, but want to know if anyone else has worked with tables with "logical delete" columns. I have added the column as an include column to every non-clustered index.
Any comments, gotchas etc, that I can use to deter this plan? or is it actually a very good idea. I have not seen it in this form in my dba travels...
TIA for all your comments...
jg
December 6, 2006 at 1:13 pm
I think a 'best practice' would be dictated by how often this situation arises.
If your users are deleting rows that you are constantly having to go back and restore, then maybe flagging the records as "deleted" without actually deleting them is a good step.
This will likely result in the need to change many of your queries so they ignore "deleted" records, of course.
Like most things, this is as much of a "people" issue as it is a "computer" issue.
December 6, 2006 at 1:43 pm
A more practical solution might be used.
Put delete trigger on the table, and on delete copy the row to an audit table.
Then you can just pull out erroeously deleted rows.
Of course does not protect you if user has ability to truncate the table.
December 6, 2006 at 5:43 pm
You can use the backups but it depends on the db size...How much space and time and it requires to restore...and also depend of backup retention on disk as well as on tape?
You can use the trigger but it will effect your delete operation, only if you want to use delete trigger data...
You can use logincal delete but as time goes table gets bigger and bigger which will impact performance...so you have to have purging strategy in place for the table...
Sooo... It all depends on the your situation and environment...
MohammedU
Microsoft SQL Server MVP
December 7, 2006 at 4:58 am
Just add a column Active_Status or IsActive. This will serve the purpose in every cases. When you have more data. more complex logic.
Trigger gives us problem whenever you will use transaction. So I dont want and slow down system
December 7, 2006 at 6:46 am
Analysis your need. How frequently user data is deleting and how frequently U need to revert back. What's a % of display the deleted data in Reports. Or ther e is another data base for Reports. What is the frequeny of transaction at your system. How large database is .....See Ur need.
1. If simply database is smaller in size, U can make Logical Delete Column.
2. U can use Mazic tables Or U can use Output.DeletedColumn and can maintain Audit table.
U need Analysis Ur System/Data.
Good Luck!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply