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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply