In an earlier article, I wrote about the differences between IIF and CASE in SQL Server. Now we will see the comparison of IIF vs CHOOSE.
IIF vs CHOOSE
| # | IIF | CHOOSE |
|---|---|---|
| 1 | IIF is a logical function which is used to return one of two values based on the evaluation of a boolean expression. IIF provides a concise method of expressing a CASE statement. | CHOOSE is a logical function which is used to return a value from a list of values based on the index value. CHOOSE works as an indexer for an array-like set of values. |
| 2 | Syntax: IIF(boolean-expression, true-value, false-value) | Syntax: CHOOSE(index, choice-1, choice-2, …, choice-N) |
| 3 | IIF uses a Boolean expression to determine which value to return. | CHOOSE uses an index value to determine which value to return. |
| 4 | IIF can only return one of two values. | CHOOSE can return one of up to 254 values. |
| 5 | IIF function returns the data type with the highest precedence from the true-value and false-value expressions. | CHOOSE function returns the data type of the value at the specified index. |
| 6 | IIF is generally slower than the CHOOSE function because it evaluates the Boolean expression for each row of data. | CHOOSE is faster because it only needs to retrieve the value at the specified index. |
| 7 | IIF function was introduced in SQL Server 2012. | CHOOSE function was also available from SQL Server 2012. |
| 8 | Examples: SELECT IIF(Quantity > 100, ‘High’, ‘Low’) AS QuantityLevel FROM PurchaseTable; | Examples: SELECT CHOOSE(ItemCode, ‘Pen’, ‘Pencil’, ‘Marker’, ‘Rubber’) AS ItemName FROM PurchaseTable; Where: ItemCode is an integer column |
Summary
IIF and CHOOSE are two logical functions in SQL Server that are used for different purposes. IIF uses a boolean expression to return one of two values, while CHOOSE uses an index value to return one of up to 254 values. They also differ in their syntax, data types, number of choices and performance of usage.
Reference
- Read more about IIF function at Microsoft Docs.
- More about CHOOSE function at Microsoft Docs.