The CHOOSE command is new in the T-SQL as of SQL Server 2012. I hadn’t ever had the chance to work with it, but after seeing someone submit a piece recently, I decided to play with it a bit.
This feature is essentially an indexing value from an array. Here’s a short example.
SELECT CHOOSE(1, 'First', 'Second', 'Three');
The first parameter to the CHOOSE() function is an integer, which is the position. The second (and subsequent) parameters are the array of values. Here’s a better example. Let’s say I have a lookup table of titles:
CREATE TABLE titles ( titleid INT , title VARCHAR(20)) ; INSERT titles VALUES (1, 'Manager') , (2, 'Developer') , (3, 'DBA') , (4, 'Sysadmin') , (5, 'Storage Admin') , (6, 'Help Desk') ;
I might potentially have a very large table. Imagine that I work at SalesForce.com or I have some type of EAV table here. If I am performing a join with employees, I could easily do this:
SELECT e.firstname , t.title FROM dbo.employees e INNER JOIN dbo.titles t ON e.titleid = t.titleid
However, suppose I have performance issues, or I’m joining to lots of tables. Perhaps I’d prefer not to actually join to another table for some reason. I could do this instead:
SELECT e.firstname , CHOOSE( e.titleid, 'Manager', 'Developer', 'DBA', 'Sysadmin', 'Storage Admin', 'Help Desk') FROM dbo.employees e
Is this useful?
I’m not sure. Most of the examples and places I can think of for using this are rather trivial, or inflexible. This seems like hard coding values into a procedure or function when a table join might be a better option.
I haven’t found any blogs that present practical uses that make sense, but I’m sure some are out there. Let me know if you know of any.
UPDATE: Rob Farley mentioned he uses this in the date dimension of data warehouses. I’m not sure if that’s the best solution, but Rob’s a smart guy, so I’ll take his word that this is a good use of the function.
Filed under: Blog Tagged: syndicated, T-SQL