>Hi,
>
>I have a feature in my app, scheduling procedure, where when user changes the schedule, the old records in a table have to be deleted and new added. In VFP version I was always "recycling" the records, that is "mark" them as "deleted" and then use them when new records have to be added. I am refactoring this procedure for SQL Server and consider doing away with "recycling". That is, delete records and let SQL Server "deal" with it. We are talking about deleting from 10 to 500 records (at the most) during the procedure. Should I still recycle in SQL Server or do away with this approach?
>TIA

Everyone has already given you the technically correct answers, so I won't repeat. However, there are a number of cases I can think of where you may want to flag records as deleted (using something like a column called DeletedOn that is a nullable date/time) instead of actually deleting a record. In some cases, it simplifies maintaining relationships between tables (for example, you have a list of choices in a table, and old records reference them but you don't want it available for new records), or helps to keep a history without introducing a data warehouse style database.

I've also used this in an app that has a table with a large number of records (300M+) that has been optimized for read performance. So it has a lot of indexes (both covering and non-covering). We found the delete performance was painfully slow, as SQL had to update all of those indexes when the delete occurred. It ended up being significantly faster to just set a "deleted" flag on the record, then filter them out of our results.

You may also want to set a flag instead of actually deleting if there is a lot of churn on the database. That is, you're always adding/deleting a large number of records in a given day. This can cause your transaction log to blow up (which can be handled with more frequent transaction log backups). In some cases that's not really ideal and in those cases you could flag them as deleted using your special column and reuse them.

Unless your app has one of those special scenarios, you're almost always better off just using a normal delete.