none
Keeping deleted Data

    Question

  • Hi All,

    I'm working on a Card Management application in Asp.net Mvc. We are using Fluent Nhibernate and Sql Server 2008.

    We are now considering to not really delete the data, when the user chooses to delete it.

    My question is, what is the beste way to do that and also what are the consequenses of this(Performance, Db size)?

    Some important tables of our database, may have thousands of records.

    Thanks in advance.

    • Moved by Naomi NEditor Wednesday, May 23, 2012 4:52 PM Can get better support here (From:Transact-SQL)
    Tuesday, May 22, 2012 9:57 AM

Answers

  • You might also want to consider, instead of a flag IsDeleted, storing DeletedDateTime as a nullable field.  In my experience, someone always wants to know both if it is deleted and when/how long ago.  Whatever you do, don't do both.

    We use the date approach, then have regular maintenance processes that slowly delete subsets of rows based on the oldest dates.  That way we can balance user demands with keeping the data correct.

    You might also want to review whether there are legal issues in keeping data that appears to have been deleted.  There may be compliance issues with doing this.

    Friday, May 25, 2012 1:42 PM

All replies

  • Hi ,

    Check it out,

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/485ca4fd-8bf3-42d2-85c2-867965e15a82/

    http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/d2cda3a3-2288-4782-ab6f-ebb56f8675e4

    As concerned about performance it is depend on the server configuration, storage , and hardware

    Tuesday, May 22, 2012 10:01 AM
  • Hi All,

    I'm working on a Card Management application in Asp.net Mvc. We are using Fluent Nhibernate and Sql Server 2008.

    We are now considering to not really delete the data, when the user chooses to delete it.

    My question is, what is the beste way to do that and also what are the consequenses of this(Performance, Db size)?

    Some important tables of our database, may have thousands of records.

    Thanks in advance.

    You can have a column called "Delete_flag" specifically for this.

    Many Thanks & Best Regards, Hua Min

    Tuesday, May 22, 2012 10:14 AM
  • Hi,


    You can do 1 thing...jst add one more column in tables like 'History' .

    When user delete data then make this column entry as 'H' >> History else keep it 'C' >> current

    When u fetch records , u can select records who have History = 'C' ...

    ------------------------------------------------------------------------------------------------------------------

    Please vote or mark as answer if it helps you...

    Thanks...!

    • Proposed as answer by Anal Patel Wednesday, May 23, 2012 4:18 AM
    Tuesday, May 22, 2012 10:15 AM
  • Hello,

    You can either use the output command to store the delete records in some other (database) table

    or 

    use a column called isdeleted(or what ever you may wan to call it) and mark as true/false depending of the delete status.

    Thousands or rows will not be a big thing for SQL server to handle, but I prefer to think to the future, when the table grows from thousands of rows to to millions of rows. (Storage could be an issue for you as well as the performance and maintenance)

    Regards
    Satheesh

     

    Tuesday, May 22, 2012 11:06 AM
  • I think a very common way to achieve this, is to have a "IsDeleted" (or whatever you call it...) column in the tables as suggested by others. It will of course mean that over time your database will contain a lot of "deleted"/old data.

    This is not neccessarily a problem, but it will mean that you ALWAYS have to include this column in your queries, updates, inserts etc. - either to include or exclude the "deleted" records.

    The advantage of this, is of course that you can easily restore deleted records - that's just a matter of changing the "IsDeleted" flag and then your record is available again.

    You can also use the above in combination with a "housekeeping" job that either copy the "deleted" records to a sort of archive database or completely delete the records. 

    If you chose one or the other option, depends on your requirement and for what reason you need to keep the deleted records. If it's for the ability for the users to undelete a deleted record, I'd opt for the "IsDeleted" flag, but if it's for legal or archiving reasons I would maybe consider moving the data to a seperate database (or table). 


    Steen Schlüter Persson (DK)

    Tuesday, May 22, 2012 12:36 PM
  • you can have IsDeleted =0 or 1 bit format as suggest by all.

    or you can create a status table and add another column with status to keep things as other status as well like active, deleted, paused, etc...

    Tuesday, May 22, 2012 12:46 PM
  • Thanks,

    Does anyone experience with Sql Server capture data change feature?

    Can i use that for my need?

    Wednesday, May 23, 2012 2:09 PM
  • Hi,

    That all depends on how you want to implement your solution, as Steen Schlüter Persson - DK said, if you are keeping the deleted data only due to few govt policies, you could think of CDC, or keeping the deleted data on separate table, or separate server (may be with a low hardware configuration which will save some cost). but if you want to restore the deleted data anytime in future using a column if the best option

    Reagrds
    Satheesh

    Wednesday, May 23, 2012 3:08 PM
  • Hi Satheesh,

    CDC works but if he needs data at any position in report or any else then it might be some more join required and it's little bit complex from my point of view...

    So adding one more column much more easier to manage...

    ------------------------------------------------------------------------------------------------------------------

    Please vote or mark as answer if it helps you...

    Thanks...!

    Thursday, May 24, 2012 4:15 AM
  • You might also want to consider, instead of a flag IsDeleted, storing DeletedDateTime as a nullable field.  In my experience, someone always wants to know both if it is deleted and when/how long ago.  Whatever you do, don't do both.

    We use the date approach, then have regular maintenance processes that slowly delete subsets of rows based on the oldest dates.  That way we can balance user demands with keeping the data correct.

    You might also want to review whether there are legal issues in keeping data that appears to have been deleted.  There may be compliance issues with doing this.

    Friday, May 25, 2012 1:42 PM