wolfkillj (1/15/2013)

---

L' Eomot Inversé (1/15/2013)

---

KWymore (1/14/2013)

---

Thanks for the 2012 question. I'm not sure why I would ever use CHOOSE. I would usually join to a reference table, or use a case statement, or create a temp table if it was a small set of values to type up for an ad-hoc query or one off report. This does seem more geared towards .NET developers. I think if I came across it in production I would have to turn the array into a reference table.

If you would sometimesd use a CASE expression for this, why object to CHOOSE? It's just a simplified syntax for CASE in a particular case. Presumably it does index into the list, so it will have better performance than case when it is applicable (because the optimiser surely isn't going to look and see if the set of values for a simple case statement provides an ungapped sequence).

Hi Tom,

Check my post above (about a page back in this thread) - the optimizer produces an execution plan with a Compute Scalar operator for CHOOSE with a defined value that is identical to that of CASE. It looks like the T-SQL implementation of CHOOSE truly is nothing but an alternative syntax for CASE when writing a conditional expression based on an equality comparison of integer values. SQL Server treats them exactly the same way.

Jason

Jason