>It appears that
>
>SET DELETE ON
>
>invokes a built in "filter" for a "delete flag" in every table record.
>
>Everything I've read suggests that I should have every expectation that a deleted record, though still physically present, should be "virtually" invisible to absolutely everything I do within a table (except of course to become "real" if I SET DELETE OFF).
>
>Is it naiive to assume that deleted records disabled by SET DELETE ON will be totally inert with respect to my app or are there some well known gotchas I should be aware of?

One problem occurs if you use primary or candidate indexes. Since the deleted record is still indexed trying to add a new record that duplicates a key value of a deleted record will cause an uniqueness violation error. This is a problem if a user deletes a record by mistake then tries to add a new record to correct the error. Using surrogate keys is the best way to avoid this problem.