CODEWITHSUNDEEP
sqlsql-serversql-execution-plan
Paritosh
Paritosh

Reputation: 4503

SQL execution plan has sort

I'm using the AdventureWorks database, ran the below query:

-- 9
select *
from Production.ProductModel
where not exists
(
    select 1
    from Production.Product
    where Production.Product.ProductModelID = Production.ProductModel.ProductModelID
);

I got back 9 records, which is correct, but when I checked the execution plan I saw there was a Sort done:

enter image description here

enter image description here

Why was a sort done? Is it coming from the inner query?

Upvotes: 1

Views: 1093

Answers (1)

Stu
Stu

Reputation: 32599

why was a sort done?

SQL Server decided to implement the exists check using a merge join - the left anti semi join in the plan.

A merge join requires both inputs to be sorted - hence it can do a single pass and efficiently match similarly sorted inputs.

SQL Server decided the additional cost of sorting the ProductModelID in order to implement the merge join would be worth the performance advantage.

You could force SQL Server to use a different join operator using a query option hint and examine the execution plan to determine if the optimizer was correct, eg option(loop join);

Upvotes: 5

Related Questions