1

I have this SQL statement:

select * 
from _my_table1 
where 1 = 0 
      and (exists (select 1 from _my_table2 where id =7))

The execution plan is:

|--Constant Scan

Now when I change my query to

Declare @i int = 1
SELECT *
FROM dbo._my_table1 
WHERE @i = 0 AND (EXISTS (SELECT 1 FROM dbo._my_table2 WHERE id = 7))

I get the following plan:

  |--Nested Loops(Left Semi Join)
       |--Filter(WHERE:(STARTUP EXPR([@i]=(0))))
       |    |--Clustered Index Scan(OBJECT:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table1].[PK___my_tabl__3213E83FEF434214]))
       |--Clustered Index Seek(OBJECT:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table2].[PK___my_tabl__3213E83F4D4FA6E2]), SEEK:([DEV-RWA_IECMS-DE_Staging_20160301].[dbo].[_my_table2].[id]=(7)) ORDERED FORWARD)

The question is why SQL Server checks for second part of WHERE clause, if the first part returns false. Is there any workaround to force the SQL Engine not to check the second part of where, if the first part returns false.

1 Answer 1

2

OPTION (RECOMPILE) is your friend here. SQL Server has no clue what the value for @i will be at execution time, so it compiles a plan that will work for any parameter value.

Forcing recompile will override that behaviour and produce a plan with a constant scan.

Example:

DECLARE @i int = 1;
SELECT *
FROM master.dbo.spt_values
WHERE @i = 0
OPTION (RECOMPILE);

If you capture the actual execution plan, it will contain only a constant scan. Without OPTION (RECOMPILE) it contains the regular plan.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.