Difference between CASE and IF | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Difference between CASE and IF

Is there any performance improvement if we use CASE WHEN instead of IF. for example, in the below mentioned queries, does ‘CASE WHEN’ perform any smarter than ‘IF’? if @aa = 1
select @b = left(@a,10)
else
select @b = left(@c,10) SELECT @b = CASE WHEN @aa = 1 THEN LEFT(@a,10)
WHEN @aa = 2 THEN LEFT(@c,10)
END Thanks,
Ram
As you see, you dont need seperate select statements if you use CASE. If you want to select data from different tables according to the conditions then If is the way to go If something
Select columns from table1
else
select columns from table2 But the above cant be written in the form of CASE … WHEN…END Madhivanan Failing to plan is Planning to fail
So, you say that, removal of an extra SELECT could gain me some second atleast millisecond. Thanks for your suggestion.
Thanks,
Ram
I think in your case there wont be any performance difference Madhivanan Failing to plan is Planning to fail
I would prefer CASE for an IF.
Having less IF loops ==> less # of branch’es ==> less number of execution plans ==> query/stored proc can run faster.
Correct me if I am wrong. ***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
Even i feel (and would prefer) you will get milliseconds improvement.
from:
if @aa = 1
select @b = left(@a,10)
else
select @b = left(@c,10) To: SELECT @b = CASE @aa WHEN 1 THEN LEFT(@a,10)
else LEFT(@c,10)
END

Don’t expect a noticeable difference when just assigning values to variables, except perhaps if you’re repeating the assignment at least thousands of times. Execution plan re-use etc. only has an effect for set-based processing (queries).
]]>