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)
4 Comments. Leave new
I just read Simon’s post about “hiding” records with thought and I remembered something.
I’ve seen couple of cases where there’s both flags used IsDeleted and IsEnabled. Former one means that the record is not ever used again, or maybe just in reporting. Latter tells that the record is hidden by someone, for some reason, and it can’t be used until it is enabled again.
Then there’s even more complex cases like price lists which have validity periods, they can be disabled/enabled and they can even be hierarchially arranged so that there is n+1 effective price lists at any given time.
Things come really interesting when you think from the user’s perspective and not just from DBA’s. User (sales person in this case) wants to know price for customer X on day Y depenging how much and what products customer orders and if there’s any discounts and/or campaings on that day :)
Hi Pinal,
Thanks for posting my comment about the soft delete. I am constantly learning, SQL Server has become such a huge subject by itself.
I believe that Flag for deleting a record is always a better option.
From a business stand point i would not like end users to directly insert records as part of undeleting a set of records as sometimes there could be some computed logic attached to few columns.
Falgging a record for deltion also will let administartors know on what is being deleted and the reasons for it which in other words at times would also help in measuring the effective usage of the system by the enduser.
Flagging helps a great deal in identifying frauds tooo from a business stand point.
There can be extensions made to this solution on whether such records marked for deletion(or deleted flag set to true) should still be maintained and if yes how and what should should be maintained .
Thank you Pinal for mentioning me, it was just a personal preference based on experience, not a correct theory by any stretch of imagination. I believe what is correct practice for any application/business has to be worked out clearly ahead of time and must include manageable size of tables and DBA related concerns with size and physical deletion.