CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008sql-execution-plan
sqlconsumer.net
sqlconsumer.net

Reputation: 485

Preferred method of T-SQL if condition to improve query plan re-use

I want to understand which is the better method of implementing a "IF" condition inside a stored procedure.

I have seen this method used extensively. Which is comparable to iterative coding...

declare @boolExpression bit = 1 --True

if @boolExpression  = 1
  select column from MyTable where group = 10
else
  select column from MyTable where group = 20

I prefer to use a set based method...

declare @boolExpression bit = 1 --True

select column from MyTable where group = 10 and @boolExpression  =1
union all
select column from MYTable where group = 20 and @boolExpression =0

I prefer to use this method because as I understand it creates a re-useable query plan and less plan cache churn. Is this fact or fiction? Which is the correct method to use.

Thanks in advance

Upvotes: 4

Views: 461

Answers (2)

Martin Smith
Martin Smith

Reputation: 453608

Assuming you are missing a UNION ALL There isn't much in it as far as I can see. The first version will cache a plan for each statement as children of a COND operator such that only the relevant one will get invoked at execution time.

Plan 1 screenshot

The second one will have both branches as children of a concatenation operator. The filters have a Startup Expression Predicate meaning that each seek is only evaluated if required.

Plan 2 screenshot

Upvotes: 4

Tathagat Verma
Tathagat Verma

Reputation: 558

You could also use it as follows:

DECLARE @boolExpression BIT = 1

SELECT column FROM MyTable 
WHERE 
    CASE 
        WHEN @boolExpression = 1 THEN 
            CASE 
                WHEN group = 10 THEN 1 
                ELSE 0 
            END 
        ELSE 
            CASE 
                WHEN group = 20 THEN 1 
                ELSE 0 
            END 
    END = 1

I know that it looks complicated but does the trick, especially in-cases when the applying of a parameter in a stored procedure is optional.

Upvotes: 0

Related Questions