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

Question: Is there a shorter alternative to a simple two-way CASE expression in SQL Server?

Answer: IIF. SQL Server has supported it since 2012, and I’m still surprised how rarely I see it used. I like it for a small either-or expression. It doesn’t replace every CASE, and it doesn’t give the optimizer a special shortcut: SQL Server translates it into a CASE expression.

Here are the two forms side by side. Both return a column named Result, and each returns TRUE for this condition.

SELECT CASE WHEN -1 < 1 THEN 'TRUE' ELSE 'FALSE' END AS Result;

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

The important caveat is how SQL handles an unknown condition. If a nullable value makes the condition unknown, IIF takes its false branch, just as the equivalent CASE WHEN ... THEN ... ELSE ... END does. Test this before using either expression to classify missing data.

DECLARE @Amount int = NULL;
SELECT IIF(@Amount > 0, 'Positive', 'Not positive or unknown')
       AS Result;

For two alternatives, choose the form your team reads most easily. Use CASE when several conditions or a more explicit decision are clearer. Both forms follow SQL Server’s data-type precedence rules, so keep the possible return values compatible. IIF can be nested only 10 levels deep, the same limit as CASE. If you write CASE for every two-way choice, what makes it preferable in your codebase?

IIF is not a faster CASE, it is a shorter one, and an unknown condition still takes the false branch.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.