CODEWITHSUNDEEP
sqlsql-serverexistsquery-performance
Bmw
Bmw

Reputation: 417

SQL EXISTS performance

I understand that SQL EXISTS checks for the existence of rows, however does it evaluate the entire expression? So for example, would something like this:

IF EXISTS (SELECT TOP 1 1 FROM table WITH (NOLOCK))
BEGIN
    ... 
END

Be faster than something like this:

IF EXISTS (SELECT 1 FROM table WITH (NOLOCK))
BEGIN
    ...
END

Upvotes: 2

Views: 542

Answers (4)

Jamie Ide
Jamie Ide

Reputation: 49301

The statements generate identical query plans so there is no difference. The second example is easier to read in my opinion.

Upvotes: 2

JNK
JNK

Reputation: 65217

Both those should run exactly the same. SQL Server takes into account that EXISTS is a short-circuited operation and doesn't evaluate the return result, just checks to see if there IS a return result!

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425843

No, it won't.

SQL Server uses TOP in the plan to evaluate EXISTS.

Upvotes: 2

Andreas Ågren
Andreas Ågren

Reputation: 3929

Exists will stop after the first hit because then the expression evaluates to true, so the top(1)-part is unnecessary.

Upvotes: 4

Related Questions