Tag: T-SQL

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.

I ran across someone that was building a restore script to automated their restores. This person wanted their script to work with any instance, and that means they’d need to find the path for the database files, if the database already existed.

It was interesting to me, and I decided to give a solution a try, and I ended up using dynamic SQL, which I don’t love, but it worked. As I was digging through, I realized that all the database data is in sys.master_files. However I’d started this and it was an intriguing problem. I don’t love this solution, and I wouldn’t use it here, but there might be a place where you can use it.

To find out if a database exists, you can easily use the sys.databases view to find it.

USE master
GO
DECLARE @path VARCHAR(500)
   , @db VARCHAR(200)
;

SELECT @db = 'AdventureWorks2008'

IF EXISTS (SELECT name
         FROM sys.databases
 WHERE name = @db
 )

This is a snippet, and you need more code for this to work, but it does work if you include the “then” and “else” blocks. If the database doesn’t exist, you can grab the default file paths from the registry if you want to build the restore script, but if it does, then what.

You want to find the database files, but these aren’t stored in master. If you query sys.database_files, you’ll get this:

I have 8 or 10 databases on this instance, but none appear. However if I query a specific database, I get the files.

How do I get this data, in a script, given that I can’t execute a “use” statement easily at runtime.

There are probably a few ways, but for me, I decided dynamic SQL might make sense her. This is an administrative task, so it’s not likely to allow for SQL Injection as I wouldn’t expose this for users to run.

The first step is to build my query. In this case, I want to execute this query:

 select physical_name
  from AdventureWorks2008.sys.database_files
   where file_id = 1

This isn’t ideal, in that I could have many files for this database, but for now I’m concerned with just getting the primary data file.

I can build this string dynamically like this. Note that I’ve assigned the result to a variable for now.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
DECLARE @file VARCHAR(500)
SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'

SElect @sqlCommand

However now I need to run this command and return a value. sp_executesql is a function that allows you to execute a string, pass in parameters, and assign them back. I can do this with this script.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
DECLARE @file VARCHAR(500)

SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'

EXECUTE sp_executesql @sqlCommand, N'@f varchar(500) OUTPUT', @f=@file OUTPUT

select @file

If I run this, I’ll get the file path from the AdventureWorks2008 primary data file.

That’s the first step in this process. If I wanted to complete it, I’d have to make sure I did this for each data file, probably using some temporary table instead of a variable, and storing all the physical paths and logical names, and using those to build a dynamic restore script.

Or I could download this script: SQL 2005 Restore Script Generator