In Denali CTP3, we have a new of new functions to play with. For this post, im going to focus on IIF and CHOOSE.
The syntax of these are :
IIF(<condition>,<true expresssion>,<false expression>)
and
CHOOSE(<value>,<when 1 expression>,<when 2 expression>………)
On the face of it these are simple replacements for :
Case when <condition> = TRUE then <true expression> else <false expression> end
and
Case <expression> when 1 then <when 1 expression> when 2 then <when 2 expression>…
First off, lets take a look at IIF.
select CustomerID,
IIF(PersonID is not null,'Person','Store')
from Sales.Customer
The intention of this code is pretty obvious, If PersonID is not null return ‘Person’ else ‘Store’ and that is precisely what it does.
CHOOSE is used like this
Select CustomerID,
TerritoryID,
CHOOSE(TerritoryID,'T1','T2','T3','T4')
from Sales.Customer
So, is that all we need to know, the functional usage. Absolutely not! This is SqlServer, knowing the functionality is a small part of the role, we need to appreciate how the underlying engine makes use of it and how that applies to our every day usage of them.
So, what we have is not so much of a new ‘function’ but a new ‘macro’. The engine has taken the IIF and converted it into a CASE. The same is true of the CHOOSE function.
As i demonstrated in my previous post, there are a couple of issues that need to be appreciated here. Primarily, the condition is being recalculated for each potential result. Ordinarily this overhead is negligible, only in a few examples has i seen a scalar computation have a noticeable effect, and that was when an estimation error caused the computation to execute many thousands of times more than necessary. However, if a udf is used, then that could be a big drain on performance. Additionally, ‘Random’ values will be recalculated and not operate as expected , in a similar fashion to my post here.
That is not to say that these are not welcome additions to the language, but ,as ever, the full functionality should be understood prior to usage.
