SQL Server IIF vs CASE
case, iif, sql-server, sql-server-2012
Solution
`IIF` is the same as `CASE WHEN <Condition> THEN <true part> ELSE <false part> END`. The query plan will be the same. It is, perhaps, "syntactical sugar" as initially implemented.
CASE is portable across all SQL platforms whereas IIF is SQL SERVER 2012+ specific.
Problem
I recently came to know about the availability of `IIF` function in SQL Server 2012. I always use nested `CASE` in my queries. I want to know the exact purpose of the `IIF` statement and when should we prefer using `IIF` over `CASE` Statement in the query. I mostly use nested `CASE` in my queries. Thanks for all the inputs.