Rajendra Gupta
Month function

SQL Server Choose() function introduction and examples

June 5, 2020 by

In the article, a CASE statement in SQL, we explored one of the important logical expressions – the Case statement in SQL Server to return the values based on the specified condition. Today, we will talk about another useful logical Choose() function in SQL Server 2012 onwards and its comparison with the Case statement.

An Overview of the CASE statement in SQL

Before we proceed with the Choose function, let’s have a quick overview of the Case statement in SQL

  • It is a logical function, and we define conditions ( in When clause) and actions followed by Then clause
  • Once the expression or value satisfies in the when clause, it returns corresponding value or expression in the output
  • If no conditions are satisfied, it returns the value specified in the Else clause

In the below example, we specify product id 1 in the variable, and it returns the output ‘Bread and Biscuits’.

SQL Case Statement

Introduction to SQL Server Choose() function

Suppose you need to specify multiple conditions in the case statement. In this case, the overall code will be lengthy. Sometimes, it is difficult to examine more extended code, and we always look for functions that could do similar work with minimum efforts and without any performance impact. Choose function does the same work for us and can be used as a replacement of Case statement.

Syntax of Choose function

We use Choose () function to return an item at a specific index position from the list of items.

Syntax of Choose function: CHOOSE ( index, value[1], value[2] ,….. value[N] )

  • Index: It is an integer that specifies the index position of the element we want in the output. Choose function does not use a zero-based indexing method. In this function, the first item starts at first, the second element at the 2nd index position, and so on. If we do not use an integer in this argument, SQL converts that into integer else returns a NULL value
  • Items: It is a comma-separated list of any type. Choose function picks the items as per the index defined in the first argument

For the index, choose function returns value[1] for index position 1. Let’s understand choose function in SQL using various examples.

Example 1: SQL Server CHOOSE() function with literal values

In this example, we use choose() function to return values from different index positions. The first select statement returns NULL because this function does not use a zero indexing method.

SQL Server CHOOSE function with literal values

Similarly, if we choose out of range index value, it returns NULL in that case as well. For example, we have five records in the above list. Let’s specify the index position as six.

Zero index value

Example 2: SQL Server CHOOSE() function with decimal index values

As specified earlier, we use integer data type in the first argument. Suppose you specify the index position as 2.5, what would be the output?

In the below, we specified multiple values in index argument having decimals. We get the same output from all the below queries. Choose() function rounds the decimal value towards lower value. In this case, all index values convert to 2 and return banana as output.

Decimal index values

Example 3: String values in the index argument of SQL Server Choose() function<

In this example, we specified index values in single quotes. It makes index argument as string values.

SQL Server converts these string values into integers, and it works similar to specifying integer values as shown below.

String values in the index argument

However, if we specify strings such as ‘two’ in index argument, It results in an error message due to data type conversion failure.

String value

Example 4: Use SQL Server Choose() function and data type precedence

Look at the following SQL code. In this, we specified integer and float data type values.

Choose() function returns the highest precedence data type in the output. A float or decimal value has high precedence over integer, so we get output in the high precedence data type as shown below.

Select SQL Statements

Let’s add a string ‘abc’, and we get the same output of Choose() function.

Output of SQL Server Choose function

If we specify index 5 that contains ‘abc’ you get an error message.

Data conversion error

We get this error because the SQL server is unable to change varchar data type to numeric data type having high precedence. You can refer to Data type precedence for more details.

Example 5: Use SQL Server Choose() function with Select SQL Statements

In the previous examples, we understood the functionality of the SQL Choose() function. In this example, we use it in the select statement to retrieve records from the [AdventureWorks] database. It is a sample database for SQL Server, and you can download a backup copy from Microsoft Docs.

We get employees’ records along with their hire date using the above query.

SQL Server Choose function with Select SQL Statements

Now, suppose we want to know the month for each employee. We can use the MONTH() function to return the month component from the date. In the below query, we specify month names and Choose() function returns the month from the list as per specified month in the index argument.

Month function

Example 5: Use SQL Server Choose() function with JSON data

We can use the Choose() function to work with JSON data as well. You can understand JSON and its different functions using the SQLShack JSON language category.

Here, we use a table-valued OPENJSON function. It returns the data type of the value in JSON data in the [type] column. In the below query, we use [type]+1 value in the index argument to retrieve the corresponding value from the list. We use +1 because [type] returns 0 for the NULL value but zero index position is not available in SQL Choose() function.

JSON data

Comparison between the CASE statement and Choose() function in SQL Server

As you might be familiar with the SQL CASE statement and SQL Choose() function as of now. You might think, we can achieve the same results of SQL Choose() function from the SQL Case statement as well.

Let’s convert the SQL query from example 5 above from SQL Choose() function to Case Statement in SQL to get the same results.

The above query returns the same results as of SQL Choose function.

Comparison from the CASE statement in SQL

You might think which one should we use? Is there any difference these two?

To get the answers to these questions, run the query with SQL Choose() and Case statement in SQL together in a query window of SSMS. Use the Go statement to separate the batches. Enable the Actual Execution Plan (press CTRL+M) to compare both queries execution plans. You can also use the compare plan feature of SSMS for it. You can refer How to compare query execution plans in SQL Server 2016 for it.

It is doing a clustered index scan for an index on [HumanResource].[Employee] table for both queries. The cost of both queries is also similar, as shown below.

Clustered index scan

Now, let’s check the properties of compute scalar function in the actual execution plan of the SQL Choose() function.

It uses the Case statement in the background. It shows both Case statement in SQL and SQL Choose() functions are the same. You can use either of them, and it does not put any impact on query performance.

The Choose() function is a shorthand of the Case statement. You can write smaller t-SQL codes to do similar using choose() function in comparison with the Case statement. We can see this difference in the SQL queries used for Case statement and SQL Choose(). You can also compare the other parameters in these plans, and all parameter looks similar.

Compute scalar properties

Conclusion

In this article, we explored the Choose() function in SQL Server to return the value based on the index position. In its comparison with a Case statement in SQL, we figured out that both functions are the same logically. If you use case statements in your query, I would recommend you to explore the Choose() function as well.

Rajendra Gupta
Development, SQL commands, T-SQL

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

5,875 Views