Hi Doug,

>>You still have to filter the candidate key on FOR !DELETED() if you use sorrugate keys.
>>
>>Therefore this problem does not have Anything to do with the use of surrogate keys. You won't solve the problem with surrogate keys !! It makes no difference whether you filter the PK or the CK on FOR !DELETED().
>>
>>Walter,
>
>You must have a very tightly controlled application to use indexes this way.

I'm not sure what you mean by that. Personally I have the standpoint that the database should be protected against inconsistency. That means that constraints which determines if a database is consistent or not, should be implemented in the database (as far as reasonable possible). This certainly applies to uniqueness constraints of primary and candidate keys (do not confuse with candidate and primary indexes).

If a user tries to enter data that makes a database to become inconsistent, the database should raise an error, which should be handled by the application.

>What would happen in the event of a recall? (of a deleted record)

A recall is considered the same as an insert (the insert trigger is called). As far as the method of using a filtered index on FOR !DELETED() is concerned, it raises the same error as trying to insert the same PK if it is already in the table. IOW whether you insert or recall a record: it makes no difference.

>Crash?

I'm not sure what you trying to say, but when a application crashes (or to be more precise: the database and tables were not properly closed) al sorts of things can go wrong with a database. I don't think this strategy is more fragile than others. OTOH by using candidate keys to enforce uniqueness is far more reliable than using a SEEK or SQL-select to check if a certain key already exists before inserting. The SEEK, SQL-select solution at best only prevent inserting duplicate keys at the programming level. Primary and candidate keys prevent the existance of duplicate keys (though I've seen someone report that he had a corrupted index which lead to two the same PKs in one table).

Walter,



I'm not sure