Voice of the DBA

Author: way0utwest

Farewell

This is my last day of work this quarter as I start my sabbatical at 5 o’clock today. I’ll be gone for the next six weeks, away from SQLServerCentral, trying to improve and grow myself while staying as unwired as I can during that time. The rest of the staff here at SQLServerCentral will pick up the load, and I’m sure everything will be fine.

This is a bit scary for me. I’ve pretty much been employed full time since I was 17, only taking a few, short, involuntary stretches of unemployment in almost 30 years. Even during those times without a job I was focused on finding a job almost every day, so it felt as if I still had the job of finding a job during those times. I’m nervous about taking the time off, and certainly worried a bit about SQLServerCentral as I’m not sure I’ve been away from the site for more than a week since it was founded.

This is a temporary farewell, as I’m planning on being back on July 14th, just in time to head to the UK for SQL Bits. I’ll likely be doing a touch of writing, and trying to get a little coding done on some side projects, but for the most part I won’t be answering email or checking on the site. That alone will be a good test of my personal growth. If I can avoid www.sqlservercentral.com, I think I’ll be doing well.

For those of you that are interested, I’ll be posting on my blog, under the sabbatical tag. I’m trying to document some of my adventures, and hopefully I’ll have something to show for my efforts at the end. I’m nervous, but also a little excited. Six weeks to work on some projects away from work seem like a dream on top of my already amazing dream job.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

May 30, 2014
Quick Tips–SSMS Select a vertical block

I saw this years ago in a presentation from Aaron Bertrand. At the time I thought it was super cool and I’d use it all the time, but I haven’t found many uses. However since I needed to do this recently, this helped.

Imagine that you have this:

A normal select statement. Perhaps you’ve qualified columns with SQL Prompt, or you’ve used some tool to enter this (or you’re a typing masochist). Now you add an alias for the column because you don’t want to type the full name everywhere. That causes SSMS to complain.

You can’t run this because once you use an alias, you need to use it elsewhere. The full table name isn’t valid anymore.

Now you could do a search and replace (CTRL+H), but that presents other problems, not the least of which is replacing the table in the FROM name. Unless you want to go through and approve or deny every replacement. You could also edit Person to “p” on each line manually.

Hey, this is programming, we don’t do things over and over when we can avoid them.

Enter Block Select

If you place your cursor here, shown with the arrow as my capture tool missed it.

Now I can click ALT+Shift and hold them down while I move my cursor to the lower right of the block I want to select. In this case, it’s between the “n” and period on the last line of the column list, above the FROM clause. Look at the image below.

I’ve now selected a block, and I can hit delete. This gives me:

Notice that my selection is a thin cursor still visible. I can actually type here. Imagine I typed “sn” now. This is what I’d get.

I fixed the alias before I shot this, and once I moved the cursor, I lost my selection, but a simple ALT+Shift, lets me highlight, select, and type in a vertical block.

Handy when trying to correct a number of items on separate lines.

May 29, 2014
No Works of Art

I used to pride myself on being able to run through menu selections and configuration options from memory. There have been times when I could describe over the phone the process for using a GUI. I’ve been able to direct people to log onto SQL Server, choose a database, right click it, and choose restore. Then select device, choose NORECOVERY, etc, etc. I could walk people through many Windows dialogs, IIS configurations, and more, with verbal commands, picturing what they would see on the screen as I was holding my phone to my ear.

People would be impressed, and I’d rarely make mistakes. I knew what was coming next, and where to click. However I did make mistakes at times, and while that’s human, that’s not what we want during software configurations. These days we find plenty of administrators and developers running an install program, and either selecting options from memory or using some sort of document that was given to them. The thing that concerns me is that any of those processes could be full of mistakes. Documents never get rev’ed to keep up with changes. Human memory is faulty, and certainly we find ourselves clicking the wrong button at times.

When we install, or deploy, software in this manner, we’re asking for problems. Not every time, but if we manually configure, click, or select anything, then we are building individual works of art. That’s not what we want from our software environments. We want to know that the production environment is configured the same way every time, and if we need to recreate it in a DR situation, we can. We want confidence that the software that was tested in the QA environment will work correctly in production, which means we know that the states of the system are the same. We might even want to be sure that we’ve configured our development environments the same as production.

As our tools get better, especially around virtualized systems, there isn’t a good excuse for not automating our installation and configuration through some sort of scripting. These days there ought to be no reason for a Windows, SQL Server, IIS, and most other platforms to not be easily scripted and deployed in a hands-free fashion. That’s one of the keys to engineering software at scale instead of sculpting it one system at a time.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

May 29, 2014
Quick Tips–SQL Prompt Qualifying Columns

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Qualifying Columns

One of the things that’s a good programming practice for T-SQL is to qualify your columns. Imagine that I have this query:

Note that my column names are listed with just the column name and don’t include the table from which they come. Not a big deal here, but as I enhance this code over time, I may add another table to a join, perhaps one that includes BusinessEntityID in it. In that case, I’ll get an ambiguous column error, and a squiggly in SSMS (shown below).

SQL Prompt tries to make writing code quicker and easier, and if I look back to my first query, Prompt can qualify those columns for me.

If I press CTRL+B, CTRL+Q, I’ll get this (from the first query).

Note that every column now includes the table names.

It also works for aliases. If I have this (note I’ve added an alias)

CTRL+B, CTRL+Q gives me this:

As I add tables and modify this code, anytime I find columns unqualified, I can use this quick shortcut to fix my code.

Note: If you have ambiguous columns, Prompt can’t fix them (yet).

May 28, 2014