CHOOSE Function In SQL Server

Choose is a logical function in SQL Server that returns the item from defined values based on the specified index. To make the definition much simpler, the choose function can resemble that of our real-life situation. For instance, a row of boxes from 1 to n consists of colored chocolates, and if we opt to choose a number, the corresponding chocolate is received.

Let’s check the similar scenario with the CHOOSE() function

General Syntax:

CHOOSE (index, 'value1', 'value2'..'ValueN')

Example of CHOOSE() Usage:

SELECT CHOOSE(0, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS 'Index value - 0';
SELECT CHOOSE(1, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS 'Index value - 1';
SELECT CHOOSE(2, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS 'Index value - 2';

Example of CHOOSE() usage returning NULL:

If the index number mentioned by you is either zero or exceeds the actual number of list values specified can result in NULL. 

SELECT CHOOSE(0, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 0];
SELECT CHOOSE(6, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 6];

Example of CHOOSE() usage bypassing string in index value:

The index values passed as a string will automatically convert them to an integer value and process the results.

SELECT CHOOSE('0', 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 0];
SELECT CHOOSE('1', 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 1];
SELECT CHOOSE('2', 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 2];

Example of CHOOSE() usage by passing a combination of integer and string in list of values:

If you notice that the SQL will process based on the index value specified. The data type precedence error would not be encountered until the index value exceeds the integer value specified. 

SELECT CHOOSE(0, 1, 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 0];
SELECT CHOOSE(1, 1, 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 1];
SELECT CHOOSE(2, 1, 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 2];

Example of CHOOSE() usage by passing a decimal at index value:

The index value passed as decimal will automatically be converted to an integer by SQL.

SELECT CHOOSE(0.9, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 0];
SELECT CHOOSE(1.1, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 1];
SELECT CHOOSE(2.9, 'swiss', 'semi-sweet', 'sweet', 'orange', 'chocobar') AS [Index value - 2];

Example of CHOOSE() verse CASE() statement comparison:

I have created a simple table as IDENTITY_TEST and inserted three rows into the table

select * from IDENTITY_TEST

Now, I have written a sample CASE statement and CHOOSE statement on IDENTITY_TEST Table.

CHOOSE:

select ID, CHOOSE ((ID),'TEN','ELEVEN','TWELVE')  AS [NUMBER] FROM IDENTITY_TEST

CASE:

select  ID,
case(number)
when 10
then 'TEN'
when 11
then 'ELEVEN'
when 12
then 'TWELVE'
end number
from IDENTITY_TEST

Execution plan of CHOOSE() verse CASE():

Please notice carefully by checking the compute scalar properties from the execution plan. The CHOOSE function, in turn, calls for a CASE. So logically speaking, both the CASE as well as CHOOSE functions are the same. They are not going to differ anywhere in terms of performance. This was just a simple case study, but please try yourself to see some interesting pieces of stuff on the execution plan behind the CASE verse CHOOSE commands.

Related Post: "Reset identity column values"

• Tweet
• Share
• Share
• Share
• Share