In SQL Server Denali, there are two new logical functions being introduced, namely:

IIF()
CHOOSE()

Today we will quickly take a look at the CHOOSE() function. This function is very simple and it returns specified index from a list of values. If Index is numeric, it is converted to integer. On the other hand, if index is greater than the element in the list, it returns NULL.

Now let us look at these examples showing how CHOOSE() works:

Example 1: CHOOSE Usage

SELECT CHOOSE ( 0, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Null;
SELECT CHOOSE ( 1, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_First;
SELECT CHOOSE ( 2, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Second;
SELECT CHOOSE ( 3, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Third;
SELECT CHOOSE ( 4, 'TRUE', 'FALSE', 'Unknown' ) AS Result_NULL;


You can see that when index is Zero or greater than the elements in the list,  it returns the value as NULL and it does not return error.

Example 2: Usage of CHOOSE when Index is Not Integer

SELECT CHOOSE ( 1.1, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_First;
SELECT CHOOSE ( 2.9, 'TRUE', 'FALSE', 'Unknown' ) AS Returns_Second;

You can see that Float value is automatically converted to Integer value and appropriate list value is selected.

Example 3: Usage of CHOOSE with Table

CHOOSE() function can be very useful when it is used in the table as well. In the following example, I am trying to figure out if the day is weekend or weekday using CHOOSE function. There are other ways to figure that out as well, but here in this example I am using that to demonstrate the usage of the CHOOSE function.

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO


In the following example, we see the result of the query listed above.

Example 4: Usage of CHOOSE with Table and CASE statement and performance comparison

Let us re-write the above query with CASE statement first.

USE AdventureWorks2008R2
GO
SELECT A.ModifiedDate,
DATEPART(dw, A.ModifiedDate) DayofWeek,
DATENAME(dw, A.ModifiedDate) DayofWeek,
CASE DATEPART(dw, A.ModifiedDate)
WHEN 1 THEN 'WEEKEND'
WHEN 2 THEN 'Weekday'
WHEN 3 THEN 'Weekday'
WHEN 4 THEN 'Weekday'
WHEN 5 THEN 'Weekday'
WHEN 6 THEN 'Weekday'
WHEN 7 THEN 'WEEKEND'
END  WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
GO

The following image demonstrates that the results from both the queries are the same.

Now let us compare the execution plans of both the query – the CHOOSE function query and CASE Statement.

When the execution plans are compared, it is very clear that the cost of both queries is the same. The execution plans also look similar. Let us now examine the properties of the Compute Scalar function for both execution plans.

When the properties of the Compute Scalar function are examined, it is clear that both of them are used at the end CASE statement. Just like IIF function, the CHOOSE function is also the shorthand of the CASE statement.

Reference:  Pinal Dave (
http://blog.SQLAuthority.com
)