Do not use the COUNT() aggregate in a subquery to do an existence check

·         Do not use:

SELECT column_list FROM table WHERE 0 < (SELECT count(*) FROM table2 WHERE ..)

Instead, use:

SELECT column_list FROM table WHERE EXISTS (SELECT * FROM table2 WHERE ...)

·         When you use COUNT(), SQL Server does not know that you are doing an existence check. It counts all matching values, either by doing a table scan or by scanning the smallest non-clustered index.

·         When you use EXISTS, SQL Server knows you are doing an existence check. When it finds the first matching value, it returns TRUE and stops looking. The same applies to using COUNT() instead of IN orANY.