You must Sign In to post a response.
  • Category: SQL Server

    Case or IIF which is better

    Can any one suggest me, using CASE or IIF which one gives better performance in SQL server.
  • #765332
    CASE expression is supported in all versions where as IIF is supported for SQL 2012 onwards.
    IIF is internally converted to CASE expression so CASE expression is slightly faster than IIF but the difference may be of few nano seconds.
    I recommend CASE expression.

    Hope it helps.
    Shashikant Gurav

  • #765335

    Hi Kavipriya,

    The CASE is slightly faster than IIF.

    IF is a control of flow statement; it indicates which T-SQL statement to evaluate next, based on a condition.

    CASE is a function -- it simply returns a value.

    DECLARE @a int

    IF datepart( m, getdate() ) = 1
    set @a = 1
    ELSE IF datepart( m, getdate() ) = 2
    set @a = 2
    set @a = 0

    SET @a = CASE datepart( m, getdate() )
    WHEN 1 THEN 1
    WHEN 2 THEN 2
    ELSE 0

    The main difference between the two is that if you use 12 nested ifs, you'll be doing 12 selects, whereas you'll only do one select with the case statement.

    Try displaying the estimated execution plan for the above code in Query Analyzer and you'll see the difference.


  • Sign In to post your comments