SQL Server Performance

Difference between CASE and IF

Discussion in 'General Developer Questions' started by ramkumar.mu, Feb 20, 2006.

  1. ramkumar.mu New Member

    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
  2. Madhivanan Moderator

    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
  3. ramkumar.mu New Member

    So, you say that, removal of an extra SELECT could gain me some second atleast millisecond.

    Thanks for your suggestion.


    Thanks,
    Ram
  4. Madhivanan Moderator

    I think in your case there wont be any performance difference

    Madhivanan

    Failing to plan is Planning to fail
  5. ndinakar Member

    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.
    ***********************
  6. ranjitjain New Member

    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
  7. Adriaan New Member

    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).

Share This Page