IIF in TRANSACT SQL on SQL SERVER 2012

For years I have heard the question of how can I do an IIF in TSQL? Honestly I never thought it would be that useful since the CASE statement is easy enough to use, but after seeing it in TSQL 2012, I can safely say I like it.

The answer used to be, just use the CASE statement, and the CASE statement in TSQL has worked great for years.

Here is the sample showing the differences between IIF and CASE, using the revenue table from the same database I used in a previous ROWS PRECEDING and FOLLOWING in TSQL 2012 example.


-- assume we want to display an indicator to see if we are above
-- or below average. First we start with the average over departmentID
select Year, DepartmentID, Revenue,
 avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
from REVENUE
order by DepartmentID, year;

-- without IIF using the CASE statement we would get the following
select Year, DepartmentID, Revenue, AverageRevenue,
 case when Revenue > AverageRevenue THEN 'Better Than Average'
 else 'Not' end as Ranking
from (select Year, DepartmentID, Revenue,
 avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
 from REVENUE ) as t
order by DepartmentID, year;

-- now the same functionality using IIF and simplifying the code
select Year, DepartmentID, Revenue, AverageRevenue,
 iif(Revenue > AverageRevenue, 'Better Than Average', 'Not') as Ranking
from (select Year, DepartmentID, Revenue,
 avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
 from REVENUE ) as t
order by DepartmentID, year;

Narrowing it down to just the differences

CASE WHEN Revenue > AverageRevenue

THEN 'Better Than Average'
 ELSE 'Not' end as Ranking

IIF(Revenue > AverageRevenue, 'Better Than Average', 'Not') as Ranking

Comparing the two, they aren’t that different, other than the IIF option is easier to remember. Also, I did analyze the plan and there is no difference in the execution time between the two.

It is going to take some getting used to, but I consider the IIF to be a simple time savings for the TSQL programmer in SQL Server 2012.

IIF in TRANSACT SQL on SQL SERVER 2012

Leave a Reply Cancel reply