Voice of the DBA

Tag: syndicated

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:
```
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.
June 4, 2013
PC Sales Declining

About 3 or 4 years ago I was talking with my wife and a colleague of hers that worked in the mobile industry. This person and my wife thought that the mobile industry would explode, and it’s impact and expansion would dwarf the PC revolution that had most of our companies buying new machines every two to three years.

I think that is part of what is happening with this article on the hemorrhaging of the PC industry. I see quite a few people moving to the mobile space, and using smart phones, texting, and more to handle the needs of their lives. The growth of tablets as well is changing the world. I see more and more non-technical adults carrying tablets to do their work. Kendall’s volleyball coach, our Scoutmaster, and more are using tablets, and ( I am guessing here), sticking with an older computer at home when they need it.

There’s also the factor that we can get so much done on any device that we don’t need that many. These days email, video, audio, browsing, and camera work can happen on almost any device.

The use of older hardware is another issue. We don’t see the fantastic pace of growth in hardware occurring anymore. I know machines get more powerful, but it doesn’t seem to be happening as quick as in the past. Part of this may be psychological with the clock speed ratings not changing as quickly, but it’s also that the technology seems to be focusing on power improvements more than anywhere else. Even the core growth has tailed off. I just bought a laptop, 4 years after my previous Windows laptop. They both had quad core chips, and while the new one seems to be faster, it’s not substantially faster.

Hardware not speeding up, tablets and phones, but there’s something else as well. Microsoft did a fantastic job with Windows 7. In many cases people are finding that the OS is faster and slimmer, without the bloat of previous versions. One can actually put Windows 7 on older hardware and it will run great. I know lots of people are still running Windows XP, which works fine, but even if they upgrade, they don’t need to move to new hardware o do so. I suspect that other OSes are in similar situations. The software is so good, there’s no reason to upgrade.

All of this isn’t good or bad. It just means a changing industry, and PC makers should both learn to adapt to new form factors (tablets), but also expect that we will buy fewer devices in the future.

May 31, 2013
Exploring a Database in Azure

After creating my Azure account, I wasn’t sure where to go next. Fortunately I had an immediate project that occupied my attention: a public Adventureworks database.

As we noted, Red Gate and SQLServerCentral agreed to host the database and cover costs. Once Jamie Thomson transferred the database to my subscription, which didn’t seem too hard, although slightly confusing, it showed up in my list of objects.

I drilled down to the sql databases to ensure this was the only object there, and it was.

I wasn’t sure what I could do, so I clicked on the database. I do tend to be an RTFM guy at the beginning, but I was curious how easy they’ve made things. This is what I saw:

It’s a good list of things, although since I have SSDT installed, I didn’t need the first item. I guess if you have SSDT installed you would know it, and the link is handy. The interesting thing for me was the “Server” string at the bottom of the panel.

I wonder if it works.

Jamie had given me the admin name and password, and I plugged this into Management Studio, along with the connection string.

Sure enough, it connected, and changing to the AdventureWorks2012 database, I could query the objects.

I decided to test Object Explorer as well, and that was interesting. I had a very cut down version of what I normally see for a SQL Server instance.

Most of the “instance” level stuff was gone. I could see the master database, and my own database, but no others.

Since this was a new instance, one of the first things I decided to do was create my own login account. I right clicked “Logins” and selected “New Login”. I go this:

A template for a script. That’s interesting. No GUI version of the login creation dialog. I used CTRL+Shift+M to fill in the template stuff.

That’s not my password, but I did choose a nice, long one. I executed the script to create a login and then went to add a user. Once again, I got a script from the GUI.

I changed this and created my user to map to my login.

This let me log into the system, and set myself as a db_owner. However I wasn’t an administrator. That’s for another post.

May 30, 2013
The 2013 SQL in the City Tour

Once again, Red Gate is planning on a tour of the US for the SQL in the City events. Last year we had a large tour, hitting 6 cities in the US during October and November of 2012. It was great fun, and very well received by the attendees. I enjoyed it quite a bit, though it was a bit of a stressful time for me with all the travel.

This year we’ve scaled back slightly, but we’re still moving around the US. We have three dates scheduled, all packed into a week: Pasadena, Atlanta, and Charlotte.

Our first stop will be in California, on Wednesday, Oct 9, 2013. Red Gate has a sales office in the Pasadena area, and we’ll have a large staff coming out to meet customers and the SQL community. This event will be at the Pasadena Convention Center, in the North East LA area. It’s close to Arcadia Wilderness Park, where I’m hoping to get in a #sqlrun

Register today and join us if you are in the area.

Atlanta has a great SQL Server community. They consistently have the largest SQL Saturday events every year and there is a lot of excitement about the platform in this area. It would be a great place for a future Summit (hint, send your feedback to PASS).

We’ll be in Atlanta on Friday, October 11, 2013 at the Blackburn Conference Center. This is at the John Marshall Law School in the heart of the city. I’m expecting this event to sell out soon, so register today.

The PASS Summit is in Charlotte this year, and once again we’ll open the week with a SQL in the City event in Charlotte on Monday, October 14, 2013. If you’re in town early, or live there, register and come spend a fun day on Monday with us, talking about lots of development and DBA SQL Server topics

You can register now to join us at the Ritz-Carlton in downtown Charlotte.

That’s our 2013 tour (along with sold out London), and I’m looking forward to seeing many of you and meeting even more this fall.

May 27, 2013