Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

The Voice of the DBA

Steve Jones is the editor of and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at

CHOOSE in SQL Server 2012

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:

( 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 or I have some type of EAV table here. If I am performing a join with employees, I could easily do this:

 , 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:

 , 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


Leave a comment on the original post [, opens in a new window]

Loading comments...