SQL Delete if the row is not affected by constraints

Question

First note that I have seen this question:TSQL delete with an inner join

I have a large table and several foreign key relations, each of which have data of a given age. We need to remove data older than a given data on a regular basis to stop the DB from growing without bound.

I'm writing a query that will delete from each point on the star if you will by the given parameters (unfortunately these are configurable and different between the tables).

After this first deletion, I have a central table that I'm worried that I'm doing twice the work attempting to delete, as on delete the database checks the conditionals. I have a set of:

AND NOT EXISTS
(SELECT key 
FROM table 
WHERE table.key = centretable.key)

which TSQL is making into a right anti semi join and doing it nicely on the indexes. The problem is it creates a list of stuff to delete and then does the same checks again as it performs the delete.

I guess my question is whether there is a try delete by row, (I'm not going to do that in a cursor as I know how slow it would be), but you would think that such a keyword would exist, I haven't had any luck finding it though.

Answer 1

In terms of a single command that only checks the relationships once (rather than twice in your example - once for the NOT EXISTS, once for the DELETE), then I expect the answer is a big fat no, sorry.

(off the wall idea): If this is a major problem, you could try some kind of reference-counting implementation, using triggers to update the counter - but in reality I expect this will be a lot more overhead to maintain than simply checking the keys like you are already.

You could also investigate NOCHECK during the delete (since you are checking it yourself); but you can only do this at the table level (so probably OK for admin scripts, but not for production code) - i.e.:

-- disable
alter table ChildTableName nocheck constraint ForeignKeyName

-- enable
alter table ChildTableName check constraint ForeignKeyName

A quick test shows that with it enabled it does an extra Clustered Index Scan on the foreign key; with it disabled, this is omitted.

Here's a full example; you can look at the query plan of the two DELETE operations... (ideally in isolation from the rest of the code):

create table parent (id int  primary key)
create table child (id int  primary key, pid int)
alter table child add constraint fk_parent foreign key (pid)
    references parent (id)

insert parent values (1)
insert parent values (2)
insert child values (1,1)
insert child values (2,1)

-- ******************* THIS ONE CHECKS THE FOREIGN KEY
delete from parent
where not exists (select 1 from child where pid = parent.id)

-- reset
delete from child
delete from parent
insert parent values (1)
insert parent values (2)
insert child values (1,1)
insert child values (2,1)

-- re-run with check disabled
alter table child nocheck constraint fk_parent

-- ******************* THIS ONE DOESN'T CHECK THE FOREIGN KEY    
delete from parent
where not exists (select 1 from child where pid = parent.id)

-- re-enable
alter table child check constraint fk_parent

Again - I stress this should only be run from things like admin scripts.