Voice of the DBA

Author: way0utwest

Surround Code with Comments in SQL Prompt

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

I can’t believe I haven’t built this snippet before now, but it’s not in my list. I’ve dealt with this for some time, but I decided enough was enough.

Here’s what I want. Note that all the code is inside a single comment.

Getting There

I often have some random notes that I want to keep in a comment. For example, I might get a list of tables like this:

If I highlight this code and hit Ctrl, I get a list of snippets that contain a certain token. In my case, I get:

If I select Comment, I get the code commented, but as single line quotes.

That’s OK, but if I am saving something like STATISTICS output, that’s not pretty or easy for me to read. I prefer a single comment, not a series of separate inline comments. This is even more annoying with code.

Let’s fix this with a snippet. Here’s my snippet code:

The $SELECTEDTEXT$ token will take whatever text you’ve highlighted and insert it into the token. In my case, I just want this commented out.

Let’s see how this works. Suppose I have this query:

I really want to get the results from STATISTICS IO and TIME as I’m tuning a few things here and I want to check how well my changes work.

Once I run this, I’d like to place the results with the query and see them to compare with the next iteration. I paste the results into the query window like this:

Now I can highlight those results and hit CTRL, type surr, and get this:

I hit tab and I have my notes commented out:

The next time I run the query, I can easily compare how things have changed:

I also use this when working through a list of results. I’ll get those in the query window, highlight them, and then surround them with a comment.

There are lots of places you might like to use this token with SQL Prompt. For more ideas, Phil Factor has a nice scenario for using this with other tokens in the Redgate Hub.

Give SQL Prompt a try today and see how it can improve coding and feel free to share your tips here.

January 31, 2018
Lowering the Noise

Over my career, I’ve had the chance to work in a variety of environments of all sizes. I’ve managed systems that powered all sorts of applications, with a variety of requirements. In many cases, I’ve had certain databases that needed constant monitoring and care from me to perform as needed. I’ve also had other systems that the business used, but weren’t necessarily critical. Those systems often had a lower priority for my attention if multiple problems occurred at the same time.

I’ve been meeting regularly with the SQL Monitor team as they try to enhance and tune the next version of their product. As the product has grown and evolved, and SQL Server advances, there are different types of counters and metrics that need to be tracked. One of the major goals is to ensure that they reduce the number of alerts for DBAs and sysadmins that don’t require immediate attention or may be unnecessarily adding to someone’s workload, at least for new installations. You might customize your system to include many alerts. To do this, we have to make decisions for the default alerts and threshholds, which can be a challenge.

That’s been a goal of mine as an administrator as well. I don’t want to get notifications or alerts of activities that are expected, such as backups. However, if backups fail, I may need to be alerted. Actually, I’m sure I need an alert, but it’s a question of whether I need to know now, at 2am, or get an alert the next morning at 9am. Often I may choose to respond differently to the QA server than I do for the production Sales instance. One might get immediate attention 24×7 while the other is a best available effort, and certainly isn’t likely to get a response on Saturday night.

While SQL Server becomes better at adapting to changing conditions, there are definitely times when a human must get involved and decide how to solve an issue. That’s the reason that many of us are employed by others. We manage tasks and make decisions that machines can’t do for us. However, our attention and time is limited. Despite what some managers think, there are only so many hours in the day and week, and we need to make decisions about which items to focus on and handle. Perhaps even more important, unnecessary distractions can weaken our focus and cause us to make mistakes that we wouldn’t ordinarily make.

As we manage more systems, many of which are important to our employers, a good system administrator will tune monitoring systems and ensure that they aren’t receiving or responding to unnecessary alerts. They work to minimize the interruptions and distractions from lower priority items. Maybe most importantly for long term job health, they ensure that systems don’t cause interruptions during their personal time, especially vacations. Good administrators put another human in the loop when they need a break.

Of course, we also do ensure that low priority systems receive some attention. We can’t ignore that low disk space warning on the development instance forever.

Steve Jones

The Voice of the DBA Podcast

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

January 30, 2018
Managing SQL Prompt Code Analysis Rules

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

SQL Prompt includes new Code Analysis rules that help you write better code. This iteration of the rules in v9 are items that are highlighted with a green squiggly line. These are basic rules, and may not apply in your environment, and you may want to disable some of these. Here’s how.

Imagine you have a simple query like this one:

As you can see, there are green squiggly lines under some code. These are static Code Analysis warnings from SQL Prompt. If I put my cursor on the line, I’ll see the warning about the old style column alias:

In this case, I know this is rule ST02, and it’s not a warning I care about. I like the equals alias construct and don’t want to constantly see this, so I’ll disable this rule.

First, open the SQL Prompt menu. There are a few new items shown below. The “Enable Code Analysis” is below the other Prompt “Enable Suggestions”. There is also the “Manage Code Analysis Rules”. Select that item.

When the dialog opens, we see a number of rules broken into sections. Each of these is numbers, and has a prefix. Best Practices have BP prefixes, Deprecated items use DEP, etc.

Scroll down to the Style rules, which have ST prefixes. We can see that ST002 is the Old-style column alias. Uncheck this box and click “Save”.

Now the rule is disabled. If I wait a couple seconds, I’ll see the green lines disappear.

There are all sorts of items that SQL Prompt tracks as a part of Code Analysis. We’re looking to understand how you use these rules, what exceptions you need, and any additional rules you’d like to see added. Send us feedback at UserVoice.

Give SQL Prompt a try today and see how it can improve coding and feel free to let me know how you like this new feature.

January 29, 2018
No Default CONTEXT_INFO()

I was doing a little testing of Row-Level Security (RLS) for the stairway, and one of the ways that some people implement RLS is with CONTEXT_INFO().

I haven’t every really used CONTEXT_INFO in production, though it’s been around for some time. This is a way of setting some session information, as this data is stored for the connection. However, since it can be reset by the connection, it’s value isn’t necessarily trustworthy from a system perspective.

If you don’t set this with SET CONTEXT_INFO, then a NULL is stored there for on-premises systems. This makes sense, there’s no initialization there.

In Azure SQL Database, however, you get a GUID that’s a unique value. That’s good to know, as if you’re checking if this is NULL, you might assume you have something stored there, and since you need to CAST this back to the original datatype, this might cause issues.

This could be a good way to store data for a single session, but beware. If the session drops and reconnects, you’ll lose your data.

January 29, 2018