What is Alternative to CASE Statement in SQL Server? – IIF Function – Interview Question of the Week #164

Question: What is Alternative to CASE Statement in SQL Server?

Answer: IIF Function.

Honestly, when I write this blog post I feel a bit sad that IIF Function has not received necessary adoption since its release in the year 2012. When I am writing this blog post it is the year 2018 and it has been 6 years since IIF function released and I see absolutely very little adoption of this feature.

Let us see the following two examples how CASE statement works and how IIF can be its alternative in some situation. Please note that CASE Statement can be very complicated in some scenarios and IIF can’t handle all the scenarios, however, most of the time CASE statement written for either this or that scenarios and IIF can be very much handy functions in those situations.

Let us see this the simple case of statement


Now let us convert the CASE statement to IIF function

SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;

Let us see how both of the queries work and provides the results.

What is Alternative to CASE Statement in SQL Server? - IIF Function -  Interview Question of the Week #164 iiffunctions

You can see that in both of the above queries if we have to make a decision between two values, it would be easy to write IIF. However, there is something preventing users to use IIF. I would love to know your opinion why do you not use IIF function instead of CASE statements?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts, SQL Server
Previous Post
How to Shrink TempDB Without SQL Server Restart? – Interview Question of the Week #163
Next Post
How to Find SQL Server Deprecated Features Used by the Application? – Interview Question of the Week #165

Related Posts

46 Comments. Leave new

  • Nikhil Mittal
    August 22, 2019 2:34 pm

    IIF takes more cost to execute than CASE …… observed practically today

  • hitesh mombharkar
    January 7, 2020 11:42 am

    Case expressions may only be nested to level 10.

  • This only works if you have a single “When” statement. In order to use IIF to accomplish the same thing, you will have all different columns, rather than the same column Name with a CASE.

    For example, if you have the following:
    When A = B Then ‘First Case’
    When C = D Then ‘Second Case’
    Else ‘Third Case’
    From TableName

    And you modify it to use IIF…
    IIF(A=B, ‘First Case’, ‘Third Case’)
    , IIF (C=D, ‘Second Case’, ‘Third Case’)
    From TableName

    The second option will give you two separate columns, rather than a dingle column that the CASE statement provides.

  • Petio Ivanov
    May 9, 2023 11:44 pm

    Why wouldn’t you just nest the second IIF, to maintain a single column?

  • pinal i have one query which have lots of case statements in select section which eventually kills the query performance.
    taking that in mind what should be the alternative


Leave a ReplyCancel reply

Exit mobile version