Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JokerOfACoder
Starting Member
18 Posts |
 Posted - 2007-05-21 : 12:25:57
|
| In my application, a user table has lots of relational data. For an example, a user may have posts in the post table, blogs in the blogs table, images in the images table etc. There maybe thousands of records.In my application, there is NEVER a physical delete..instead a record is marked with a status column that states it's deleted and all select queries filter it out.If a user deletes his record, I use a transaction and "delete (not physically)" the blogs,images,posts etc of the user. Is this efficient? Also, every week, I want to run an application that starts PHYSICALLY deleting records that are marked as deleted. In a large application, this would take a lot of power.....so is there a "background" processing technique in .NET? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-21 : 12:44:04
|
| >> Is this efficient?Can't you just mark the user as deleted and change the selects to always join to the user record?Would be better to do it using a scheduled job in sql server. Delete a batch of rows at a time so that it doesn't lock the table or anything for too long.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pvedi
Starting Member
6 Posts |
Posted - 2007-05-21 : 17:08:17
|
| You can archived the deleted records in an archived table by writing a delete trigger. Truncate it at the end of the week, just a thought. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 01:50:01
|
| Why do you mark them as deleted, rather than physically deleting them? Is this to provide an "undelete" function?Otherwise I would be inclined to just delete them. The bulk delete once a week is going to be a more resource intensive task, although you can schedule it for "quiet time" of course.There's some discussion of the most efficient methods of bulk deletion here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Bulk+Delete+of+RecordsKristen |
|
|
|
|
|
|
|
|
Background Processing - Cleanup Records