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
SELECT CASE WHEN -1 < 1 THEN 'TRUE' ELSE 'FALSE' END AS Result;
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.
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)
46 Comments. Leave new
Is there any performance impact of using one vs the other..
Why people don’t use IIF
1. IIF is available from SQL Server 2012
2. CASE is more flexible
3. Although it is not true people believe that IIF first calculates both true and false values before the condition
Personally, I can say the biggest reason is that I was unaware of it. However, even now that I am, I will give you a few reasons I may not adopt it in some, or at least all cases.
1. Older code that was written years ago. It will never hit the priority list to scrub my stored procedures to replace one syntax for the other. Especially because the CASE style IF block is (debatable if it is) more self-commenting.
This brings me to #2…
2. I don’t like using two different types of syntax for the same thing in my code/projects. I don’t think it looks that clean. It always begs the question, if only for a second, of why someone chose one way over the other when you mix.
Where will I use it?
It seems from your article that the only benefit is a more compact syntax (you didn’t mention performance in your article). So, in truth, I might start to pepper it in where I am setting up Boolean variables at the top of a procedure. Compact syntax is key there and it will be nice to not have it be multi line variable setting and still be readable.
I have been using it, lately in some of my queries, I don’t recall how I learned about it, but I like it. A funny story on this, I happen to be reviewing a view I have written,and I noticed the IFF, I thought to myself, I forgot you can do this, I guess the moral of story, that I am so use to using a case statement that it comes natural when writing a new query.
2 reasons I can think of: 1 I never knew this function came out. 2. Adaptation of new functions are slow in my enterprise’s environment, since a majority of our SQL server are still 2008 R2, some with 80 compatibility running.
The product I work on only recently stopped supporting SQL Server 2005, and we still support 2008. We can’t use new SQL functions until the minimum supported version supports them.
IIF isn’t part of the SQL standard and there isn’t a compelling reason to disregard that other then it’s a little easier to write. IIF may be easier but CASE is more portable and in my opinion more clear. If IIF performed better then a Case statement or added new functionality I’d consider it but it doesn’t. Those are my two cents anyway.
IIF is not supported in oracle sql. Easier to know 1 function and its syntax.
Is there a performance boost to using IIF over CASE? If not I see very little purpose to it. Sure it might be quicker to put one in, but you lose the range of functionality.. plenty of times I’ve come back to add conditions to an old query which would end up forcing a rewrite
People are logically used to writing the case statement. They have logically accepted the case and it is harder to create or study new logical explanation and remember IIF.
Nested iifs are much more difficult to interpret than case statements.
My use of CASE might come from a different need to some, but IIF doesn’t work for it.
I don’t use CASE often but when I do it’s because I want to see friendly values instead of codes when going data validation/testing. For example, I don’t want to see 0, 1, 2, 3, 4, 5 in the query results, I want to see things like New, Pending, In Progress, Approved, Closed and Rejected. I’d not heard of IIF until this post but it looks like it doesn’t work when you have more than two possible results.
I avoid to use CASE statement always that is possible. Instead I create a sub inner join with a pair of value – return columns.
I use both if in some simple scenario you mentioned but the fact that you have to provide value for Else condition every time you use iif doesn’t convince me to use it very often.
CASE is an expression, not a statement.
I don’t use IIF because it becomes a mess when you have to account for more than two options. I’d rather keep things more readable and future proof by sticking with CASE so I can add, subtract, or change output options easily. I can’t think of an option where IIF would be better.
The problem is that IIF was borrowed from ittheir Microsoft languages. As your example shows, people use it wrong, because in SQL the the alternative to TRUE is NOT TRUE, which is FALSE OR UNKNOWN, not FALSE.
Seriously, I’m using sql server since version 4.21. This feature never got my attention… until today.
thanks.
So which one is faster? Let’s say you have millions of rows and your Where is limiting the result set to a couple hundred thousand and your condition is comparing a column in the result rows with a literal. Do both functions get processed the same way by the SQL engine? Would that be before or after the WHERE has limited the result set?
Also, I always wondered if ISNULL is also just as fast or faster. I indeunders is has a more limited scope on when you can use it but it would be interesting to know when to use any of the three for performance. Otherwise the new syntax seems like a waste of time to adapt to, especially when you still have to support SQL2008 R2 environments.
Case statements replace nested IIf statements. They allow for easy to read logic in multiple decision steps. For a simple if this then that the IIF works perfectly but not when several steps are required. An example is converting a numeric date to a 3 digit month name.