In SQL Server 2012, there are two new logical functions being introduced, namely: IIF() and CHOOSE(). Today we will quickly take a look at the logical 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 the 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 a weekend or weekday using the 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 (https://blog.sqlauthority.com)
14 Comments. Leave new
thanks pinal
Hi,
really we good function and good article with great description.
Thx
vinay Kumar
Good article.. But, images seems to be BROKEN!!
what is Denali here
Vaishali,
Denali is the code name for the upcoming release of Microsoft SQL Server.
The product is currently not available in the market, but is under CTP (Community Technology Preview).
Interesting function.
Thanks for post.
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”.
Great Post sir, It will helpful for all.
Good Post. Thanks for sharing sir.
if both choose n case works as same with same cost then is there any specific advantage of choose function.
SIR U ARE GR8 HO….
very simple and good post pinal thanks!!!
Gr8 Post..
Why SQL 2012 name Denali??? Is any story behind it?
Thanks Sir for a simple to understand post.