Voice of the DBA

Category: Blog

In a basement, a long, long time ago… #tsql2sday

It’s T-SQL Tuesday time and this is a good one. Read the invitation and proceed.

Who Influenced Me?

I’ve been working SQL Server for a long time. Since 1991, in fact, and had held 4 jobs as a DBA or developer by 1999. I was comfortable with the platform, and had actually published some articles in early 1999 for a small UK journal. My career was going well and I felt very comfortable with the platform (v6.5 in my company) at that time.

As a developer, I knew T-SQL well, and was still learning things from reading articles, but there weren’t many SQL problems I couldn’t come up with some solution for. As a DBA, I knew the tricks to keeping a large, busy instance running, including the DR tricks to ensure my restores worked well on new hardware.

I was lucky enough to get my company to fund a trip to the initial PASS Summit. I went, along with my wife and infant son, to visit Chicago. We went to Comiskey Park on the last ,day of the baseball season, braving chilly weather along with about 1,500 people to watch the White Sox. I’m not sure my wife has been to a game since…

In any case, my main memory of that event was being the basement of some hotel in downtown Chicago. There were a few rooms in use, and we packed into see sessions on various topics. One was delivered by Kalen Delaney, @sqlqueen, who was perhaps the person I saw as an amazing speaker and teacher. I’d read her Inside SQL Server 7.0 book, anticipating an upgrade. I was delighted to see her speak, and perhaps more thrilled to shake her hand and get an answer to a question.

That was quite a highlight in my career.

Since then I’ve seen Kalen speak many time, and we’ve become friends. I’m always glad to get a hug and a few words with her, but each time, I remember that early, semi-awestruck time when I met her.

She’s inspired me, both as a data professional and a teacher.

If you ever get the chance to meet Kalen, take a moment or two with her. You won’t regret making the effort to introduce yourself and ask a question. I know I haven’t.

November 14, 2017
Changing the Default DB for a Login–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

Then, as a sysadmin, I right clicked to attach a database back.

and all of a sudden I get this error:

Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:

ALTER LOGIN [PLATO\Steve] WITH DEFAULT_DATABASE = MASTER

If you are on an older version, something like this will work:

exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

Once that was done, the GUI dialog worked. A quick and easy fix in this case.

SQLNewBlogger

As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10 grabbing a few screenshots and putting this post together.

You could do the same thing. Show that you can recover from errors.

November 13, 2017
Off to DevOps East

The travel countdown is down to 2 trips this year. The second to the last is this week, as I’m off to DevOps East, a conference in Orlando, FL that tries to bring together lots of DevOps experts and advocates to share ideas and learn more. This is my first time at this event, and I’m looking forward to talking DevOps for a few days.

I’ll be in Orlando all week, and then I have a month off before one last trip for SQL in the City.

If you’re at the conference, say hi. And certainly talk databases with everyone that you see.

November 7, 2017
ROLLing UP Totals–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article recently that talked about ROLLUP, and I wanted to play with this a bit more. I hadn’t used this much in my career, but it’s a handy operator that’s worth including in your toolkit.

ROLLUP is used to provide additional totals for your aggregates while using GROUP BY. Here’s a little example. I’ve got some data for American Football quaterbacks. In this case, I’ve extracted some stats for a few noteworthy players today. Here’s a sample:

I want to aggregate some data together. For example, let’s say that we want to look at the total touchdowns scored per year by these players in their first 5 years. That’s a simple query:

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY qs.CareerYear

This gives me data like this:

However, when I look at this, I don’t have a total across all years. In my set, there are players that have played up to 18 seasons, and I want to know the aggregate number of TDs. I can get that by adding WITH ROLLUP. This is added

after the group by. However, I can also change this to GROUP BY ROLLUP and include the columns in parenthesis.

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY ROLLUP (qs.CareerYear)

This is OK, but the NULL isn’t great. What can I do here? I can add an ISNULL or COALESCE to my query and get this:

Useful, but what about if I’m aggregating by individual players? I can add the player to the column list, but I also need to add the player to the ROLLUP (or GROUP BY) list as well. If I do that, I get this:

That’s quite a bit more data, and I now see NULL values in the Player name. What’s this?

These are the totals for that subgroup. In this case, the first NULL above, next to the 1, is the total for all players for season 1. I can use another trick to clean this up:

This now shows some data in an easier to understand fashion, with some totals. We can see for season 1 that 3 players really started their careers in a strong fashion and 3 didn’t.

At the end, however, we get a different result.

This is because we have handled the player name as a NULL for the second column, but not the season as a NULL. For this last row, out “Total” text comes from an ISNULL of the first column. Here we’d want to do this:

This is where COALESCE comes in handy, allowing us to account for multiple NULL values.

ROLLUP is a nice way to get totals for each of your GROUP BY columns. This can make some of your reporting easier, and perhaps even faster by having the totals calculated in the result set.

SQLNewBlogger

This was a fairly easy piece to write. It took longer to get data together and find a set that had two grouping elements and was interesting than write the queries. I also had to play with ROLLUP a bit to clean up the NULLs, which always made this seem like a useless operator.

I think I’d look to use this more if I had the chance for many reports, as summing totals in some client tools is a pain.

November 6, 2017