In this blog I’ll look at EXCEPT vs NOT IN performance differences.

EXCEPT command introduced in SQL Server 2005 is used to compare two tables/view/sub queries. It returns distinct rows from left result set which are not present in the right result set.  The NOT IN operator does the same however it doesn’t returns the distinct result set.  Let’s look at an example

Execute the below script to create dummy tables and insert rows.

Let’s now find all col1 values from tblone which are not in tbltwo using EXCEPT and NOT IN operator and compare the result.

1_Except vs NOT IN performance in SQL Server

The EXCEPT returns distinct rows whereas NOT IN didn’t return distinct values. Let’s now analyze the execution plan for both the queries.

2_Except vs NOT IN performance in SQL Server

As shown in above snapshot, the EXCEPT query is slower than NOT IN. The distinct sort operator in the EXCEPT costs 63% of the total execution time.  Let’s create a non-clustered index on tblone.col1 and then analyze the execution plan.

3_Except vs NOT IN performance in SQL Server

As shown in above snapshot, the performance of EXCEPT and NOT IN is same as the distinct sort operator is replace by the index scan on tblone.ix_col1.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook