sqlsql-server-2008t-sqlquery-optimization

Using T-SQL EXCEPT with DELETE / Optimizing a query

The following code removes records of tasks related to inactive projects from the table.

delete from [Deliverables] where 
[Deliverables].[ProjectID] not in 
(
select 
[ProjectID] from [ActiveProjects]
)

I've read somewhere that using NOT IN with subquery that returns a lot of values is not really the most efficient thing to do and it's better to use EXCEPT clause.

However, when I try to use the following code, I get an error (Incorrect syntax near the keyword 'except'.)

delete from [Deliverables]
except
select * from [Deliverables], [ActiveProjects]
where [Deliverables].[ProjectID] = [ActiveProjects].[ProjectID]

How can I use EXCEPT with DELETE? If I can't, is there any way to optimize my query to execute faster?

Solution

  • You can try as well not exists and script would look like:

    delete from [Deliverables] 
where not exists 
    (select 1 
        from [ActiveProjects]
        where [ActiveProjects].[ProjectID] = [Deliverables].[ProjectID])

    If there is a lot of data in [ActiveProjects] then it should be better solution, however it is all data dependent so please test efficiency before use.