SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

July 2011 - Posts

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>)


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


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,
from Sales.Customer
And again is obvious in functionality.

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.
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.

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.

Posted: Jul 19 2011, 01:30 PM by Dave Ballantyne | with no comments
Filed under: