In SQL Server, there are various functions available to perform logical operations. One such function introduced in SQL Server 2012 is the CHOOSE() function. This function allows you to select a value from a list based on a specified index.

The CHOOSE() function is quite simple to use. It takes an index and a list of values as parameters and returns the value at the specified index. If the index is numeric, it is converted to an integer. If the index is greater than the number of elements in the list, it returns NULL.

Let’s take a look at some examples to understand how the CHOOSE() function works:

Example 1: Basic Usage

In this example, we will use the CHOOSE() function to select values from a list based on the index:

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;

As you can see, when the index is zero or greater than the number of elements in the list, the function returns NULL without throwing an error.

Example 2: Handling Non-Integer Index

The CHOOSE() function automatically converts a float value to an integer value and selects the appropriate list value. Here’s an example:

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

In this case, the float values are converted to integers and the corresponding list values are selected.

Example 3: Usage with Tables

The CHOOSE() function can also be used with tables. In this example, we will determine whether a day is a weekend or weekday using the CHOOSE() function:

USE AdventureWorks2008R2;

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

In this example, we join the [Person].[Address] table with the [Person].[StateProvince] table and use the CHOOSE() function to determine whether each day is a weekend or weekday.

Example 4: Performance Comparison

Let’s compare the performance of the CHOOSE() function with a CASE statement. Here’s the same query using a CASE statement:

USE AdventureWorks2008R2;

SELECT A.ModifiedDate,
       DATEPART(dw, A.ModifiedDate) AS DayofWeek,
       DATENAME(dw, A.ModifiedDate) AS 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 AS WorkDay
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP ON SP.StateProvinceID = A.StateProvinceID;

Both the CHOOSE() function and the CASE statement produce the same results. When comparing the execution plans, we can see that the cost and structure of both queries are similar. The CHOOSE() function is essentially a shorthand version of the CASE statement.

Overall, the CHOOSE() function is a useful tool in SQL Server for selecting values from a list based on an index. It can simplify your queries and improve readability. Give it a try in your next SQL Server project!