SQL SERVER – Denali – Logical Function – CHOOSE() – A Quick Introduction

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)

About these ads

19 thoughts on “SQL SERVER – Denali – Logical Function – CHOOSE() – A Quick Introduction

  1. Great post, Pinal! As always, clear, concise & simple!

    Although the CHOOSE() is just a wrapper around the CASE statement, it makes a query readable, which to me, is a great advantage point. With Denali, Microsoft has ensured that working with SQL becomes simpler. Thank-you, Microsoft.

    Also, I would like to thank you for the values used in Example #2. The example clearly demonstrates that while floats are converted to INT for the conversion, the rounding is downward and that is conveyed by use of the value “2.9″.

  2. Pingback: SQL SERVER – Denali – New Functions and Shorthand for CASE Statement Journey to SQLAuthority

  3. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  4. Pingback: SQL SERVER – Denali – New Functions and Shorthand for CASE Statement « Journey to SQLAuthority

  5. Pingback: SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012 « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s