Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Denali–IIF and CHOOSE

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.

image

CHOOSE is used like this

Select CustomerID,
TerritoryID,
CHOOSE(TerritoryID,'T1','T2','T3','T4')
from Sales.Customer
 
And again is obvious in functionality.
 
image
 

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.
 
The execution plan is our best window into this area, so lets start looking at the plan for the IIF example above.
 
image
 
Nothing surprising, a ‘Clustered index scan’ as we are fetching all the rows and a ‘Compute Scalar’ operation to support the ‘IIF’.  Lets take a look at the properties of scalar function.
 
image

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.

image

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.

Comments

Posted by mtillman on 22 July 2011

Thanks for the interesting post.

Posted by cmille19 on 24 July 2011

The IIF statement looks a bit a like a ternary operator en.wikipedia.org/.../Ternary_operation

Posted by Joe Celko on 24 July 2011

What a great way for cowboy coders to write non-portable code and get locked into Microsoft. Now I need to write a macro in my code formatter to clean out this insanity so SQL programmers can read the code.

Posted by erico on 25 July 2011

It may be fair to say that the IIF and CHOOSE functions are nothing other than CASE made simple. No much improvement Performance wise.

Leave a Comment

Please register or log in to leave a comment.