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

Question

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

Are you missing a `UNION ALL` in the second one? – Martin Smith Oct 05 '11 at 09:43 — Martin Smith, Oct 05 '11 at 09:43
Yes, thanks Martin, UNION ALL is missing. I'll add it in – sqlconsumer.net Jan 16 '14 at 10:50 — sqlconsumer.net, Jan 16 '14 at 10:50

Martin Smith · Answer 1 · 2011-10-05T10:59:12.773

4

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.

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.

edited Oct 05 '11 at 10:59

answered Oct 05 '11 at 09:33

Martin Smith

438,706
87
741
845

the bit parameter has either 1 or 0 values so why union when one of the select statements will always return 0 rows? – Adrian Iftode Oct 05 '11 at 10:44
1

@Adrian - So only one result set is returned. Instead of returning 2 result sets - One empty and one containing data. – Martin Smith Oct 05 '11 at 10:48
The question was really about query plan re-use. My example included the most basic query to illustrate my request. In reality much more complex queries will be in question. – sqlconsumer.net Oct 28 '11 at 07:53
@sqlconsumer.net - Well it doesn't make any difference for the case in your question and you haven't explained how your real situation differs from your stated situation. – Martin Smith Oct 28 '11 at 10:07
So will this method allow the stored procedure query plan to be more re-usable? – sqlconsumer.net Nov 15 '11 at 08:51

score 0 · Answer 2 · answered Oct 05 '11 at 10:31

0

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.

answered Oct 05 '11 at 10:31

Tathagat Verma

549
7
12

This is unsargable and will give a scan not a seek. – Martin Smith Oct 05 '11 at 10:36
@Martin Smith: Thank you for your comment, I agree that it's gonna be a scan. Just wanted to depict another way for conditional operation in the WHERE clause, because it's more easy to maintain for changes, until and unless performance becomes a real concern, which would be subject-to-change w.r.t actual-need/requirement; which wasn't really clear in the question. – Tathagat Verma Oct 05 '11 at 10:51

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

2 Answers2