Last Week I wrote article about SQL SERVER – Soft Delete – IsDelete Column – Your Opinion and this article has got excellent community response. There have been some very interesting feedback on both the side. There are few opinions where expert have explained the conversation very balanced way. I am listing today here few of the conversations. You are welcome to provide further input on the same subject. I am listening here only abstract of the comment, click on the name to read the complete comment.
jonmcrawford – She has very first very good explanation and votes for no, suggesting correction in design.
Karen Lopez – This one liner says all. “But setting a deleted flag for one-off delete instead of a real delete when the business means delete is just not cricket.”
Tyler Clendenin – “I use flags frequently because end users seem to rarely consider the ramifications before deleting a record. He follows up with excellent question regarding taking care of transactions.”
Phani – “So my point is IsDeleted kind of columns are very handy in tables which are used for metadata setup.”
Marko Parkkola – He comes with up excellent usage case. “So instead of deleting we have IsDisabled field in the Employee table which tells that “this row in this table is not valid anymore but all the records referencing this row are valid”.
David Ames – “PS – instead of OrderIsDeleted as a bit column, I usually use an OrderDeletedDate column then have a second process that purges deleted order records a month after the deleted date. – This handles the case where the user hits the wrong button.”
John McLusky – “It depends very much on the nature of the table and your data.”
Feodor – “Bottom line: we cannot simply disregard a technique just because it seems poor at first sight. If a technique exists and it has been in use for a while, then there must be something useful about it.”
Wilfred van Dijk – “PostGreSQL have the famous ‘VACUUM’ command to reclaim space by records marked as deleted “
Nakul Vachhrajani – “The use of an archive table was debated I am aware, but it was later eliminated because items are not “Deleted”, we now call them “retired” – they can be reintroduced.”
robert cook – Robert shares something similar to my personal experience. “The thing I most disliked about the practice was the requirement to add a where clause to every SQL statement to ensure the “deleted” row was always ignored. On more than one occasion a reporting discrepancy was caused because this filter was missing.”
Paulo – Paulo comes up up very interesting question for view users. “What I do in this situation is to just flag the rows that have to be removed, and index the flag column (to have good performance accessing the active rows) and create a view that only shows the active row.”
JianHou – Jian has expressed his organization’s current architecture. “So yes, nothing ever gets deleted in the database when it is deleted from the application.”
R A Valencourt – has excellent question regarding partition table.
Husain – Husain asks the question regarding foreign keys “how would deleting a record from the Orders table and inserting it into an ArchivedOrders table work if another table has a foreign key reference on the OrderID column of the Orders table?”
Malathi – She is an experienced DBA and expressed her opinion here – “As a DBA i definitely prefer a flag to restoring a huge databse for someone who did a careless delete!!”.
Jeff – Jeff shares his experience on performance here. “With more than several million records in some tables using this structure, we are still not seeing any significant performance hit.”
Kenny Eliasson – “I more have a problem with the word “IsDeleted”. It’s not deleted, it’s “discontinued”, “quitted” or something with a more fitting name.”
Ramdas – “So i feel it depends on business needs where in in some situations soft delete kind of methods have to be employed.”
Simon – Simon has very good argument here. “Basically “Delete” to the user really means “Hide” and isDelete solves this problem. It would be possible to automatically archive “mistakes” by identifying records with no relationship to any other table.”
Greg – Greg has suggestion of Un-Delete procedures and it is very interesting to read about it.
This conversation is yet not over. What is your opinion?
Reference: Pinal Dave (https://blog.sqlauthority.com)
