Pinal Dave
SQL Interview Questions and Answers
46 Comments
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 complicatedin 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
SELECTCASEWHEN-1<1THEN'TRUE'ELSE'FALSE'END ASResult;
Now let us convert the CASE statement to IIF function
SELECTIIF( -1<1,'TRUE','FALSE')ASResult;
Let us see how both of thequeries work and provides the results.
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
Related Posts
SQL SERVER – gMSA: The Service Did Not Start Due to a Logon Failure
SQL SERVER – Find Hostname and Current Logged In User Name
SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time
46 Comments. Leave new
Nikhil Mittal
August 22, 2019 2:34 pm
IIF takes more cost to execute than CASE …… observed practically today
Replyhitesh mombharkar
January 7, 2020 11:42 am
Case expressions may only be nested to level 10.
ReplyKeith Hay
February 8, 2023 2:19 am
ReplyThis 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:
Select
Case
When A = B Then ‘First Case’
When C = D Then ‘Second Case’
Else ‘Third Case’
End
From TableNameAnd you modify it to use IIF…
Select
IIF(A=B, ‘First Case’, ‘Third Case’)
, IIF (C=D, ‘Second Case’, ‘Third Case’)
From TableNameThe 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?
ReplyKamran Shahid
November 13, 2023 11:49 pm
pinal i have one query which have lots of case statements in select section which eventually kills the query performance.
Reply
taking that in mind what should be the alternative
« Older Comments
Leave a Reply
Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.
Pinal has authored 13 SQL Server database books and 69 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,700 database tech articles on his blog at https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at [email protected].
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.