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’.
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.
|
1 2 3 4 |
SELECT CHOOSE(0, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Zero Index value'; SELECT CHOOSE(1, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'First Index value'; SELECT CHOOSE(2, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Second Index value'; SELECT CHOOSE(3, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Third Index value'; |
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.
|
1 |
SELECT CHOOSE(6, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Sixth 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.
|
1 2 3 4 |
SELECT CHOOSE(2.10, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.1 Index value'; SELECT CHOOSE(2.40, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.4 Index value'; SELECT CHOOSE(2.59, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.5 Index value'; SELECT CHOOSE(2.99, 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS '2.99 Index value'; |
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.
|
1 2 |
SELECT CHOOSE('1', 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Index value' SELECT CHOOSE('5', 'Apple', 'Banana', 'Mango', 'Grapes', 'Strawberry') AS 'Index value'; |
SQL Server converts these string values into integers, and it works similar to specifying integer values as shown below.
However, if we specify strings such as ‘two’ in index argument, It results in an error message due to data type conversion failure.
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.
|
1 |
SELECT CHOOSE(2, 5,10,3.69, 29) as [Output] |
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.
Let’s add a string ‘abc’, and we get the same output of Choose() function.
|
1 |
SELECT CHOOSE(2, 5,10,3.69, 29,'ABC') as [Output] |
If we specify index 5 that contains ‘abc’ you get an error message.
|
1 |
SELECT CHOOSE(5, 5,10,3.69, 29,'abc') as [Output] |
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.
|
1 2 3 4 5 |
SELECT top 10 [NationalIDNumber] ,[JobTitle] ,[HireDate] ,[MaritalStatus] FROM [AdventureWorks].[HumanResources].[Employee] |
We get employees’ records along with their hire date using the above query.
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.
|
1 2 3 4 5 6 7 |
SELECT top 10 [NationalIDNumber] ,[JobTitle] ,[HireDate] ,CHOOSE(MONTH([HireDate]),'January','February','March','April','May','June', 'July','August','September','October','November','December') As [HireMonth] ,[MaritalStatus] FROM [AdventureWorks].[HumanResources].[Employee] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT [Key], [Value], [Type], CHOOSE(type+1,'Null','String','Integar','Boolean','JSON Array','JSON Object') AS JsonType FROM OPENJSON(N'{ "ID":null, "Name":"Rajendra", "EmpID":3, "IsActive":false, "Departments":["IT","Admin"], "EmployeeObject":{ "City":"Delhi", "Country":"India" } }'); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT TOP 10 [NationalIDNumber], [JobTitle], [HireDate], CASE(MONTH([HireDate])) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'July' WHEN 7 THEN 'August' WHEN 8 THEN 'September' WHEN 9a THEN 'October' WHEN 10 THEN 'November' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END 'HireMonth', [MaritalStatus] FROM [AdventureWorks].[HumanResources].[Employee]; |
The above query returns the same results as of SQL Choose function.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SELECT TOP 10 [NationalIDNumber], [JobTitle], [HireDate], CASE(MONTH([HireDate])) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'July' WHEN 7 THEN 'August' WHEN 8 THEN 'September' WHEN 9 THEN 'October' WHEN 10 THEN 'November' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END 'HireMonth', [MaritalStatus] FROM [AdventureWorks].[HumanResources].[Employee]; Go SELECT top 10 [NationalIDNumber] ,[JobTitle] ,[HireDate] ,CHOOSE(MONTH([HireDate]),'January','February','March','April','May','June', 'July','August','September','October','November','December') As [HireMonth] ,[MaritalStatus] FROM [AdventureWorks].[HumanResources].[Employee] |
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.
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023