Voice of the DBA

Author: way0utwest

What’s the little popup window in #SQLPrompt?

Awhile back I was working in SSMS and saw this window.

It threw me off since I was trying to write some code and hadn’t expected it. I clicked Escape, Enter, a few things and was getting frustrated when it disappeared.

I ignored it until I saw the window again and then investigated. I’m glad I did because I was able to answer a question from someone else recently that didn’t know how to get rid of it.

Tl;Dr CTRL will make it appear or disappear.

When I am working with SQL Prompt, it’s in the background. I usually just depend on it to pop up some code or give me information. This means when I have a cursor, there’s no sign of SQL Prompt. Notice this below.

As soon as I select an area, as little as one space, I get a small SQL Prompt window in the left sidebar. As you can see in the image below, this has a down arrow on it.

I can click on this, but being a keyboard person whenever possible, I accidently discovered that CTRL will expand this, as shown below.

What threw me initially is that not all my snippets are in this list. Only those that have the $SELECTEDTEXT$ token inside them. These are handy snippets that I want to use to encapsulate text.

For example, let me surround a simple query.

I see the SQL Prompt icon and can click CTRL to open the list. If I type “cv”, I get the Create View snippet.

Once I then hit tab, I get the snippet with my query inside.

This is especially handy with things like TRY..CATCH, where I can write the TRY part and then quickly surround it with the structure.

Once you get used to this, and learn not to habitually tap the CTRL key (as I do), you’ll find this list of snippets handy. And if you don’t like them, just tap CTRL and get rid of the list.

November 30, 2016
The Pressure to Compromise Ethics

There have always been those that seek to defraud, deceive or mislead their customers. In order to do this, someone inside an organization has always been willing to pressure employees to compromise their ethics and morals. Construction companies may use substandard materials or ignore standards for construction. Financial companies falsify performance records of products. Medical professionals may order unnecessary tests to charge more. Perhaps one of the common areas many people feel cheated is with used vehicles, where there are numerous stories of deceit from companies and individuals. I’ve had my own experiences with cars to support this.

It may be no surprise to you this is also happening in the digital world, which seem fundamentally more disturbing to me. After all, software is constantly changing in a way that many other industries don’t. We can fundamentally rewrite the rules under which systems work by deploying new code, something many of us do on a regular basis. What’s more, we have rules and regulations that fundamentally prevent us from disclosing how the systems work, limitations that don’t allow for anyone to easily audit or evaluate what the programming might be doing.

This is different than a car, where we can examine the components and test them, or have a third party perform this for us. It’s different than health, where we can solicit second opinions. Even construction has inspections from independent groups that can verify some specification is met. However, none of that exists for software. In fact, our industry resists remotely attempting to implement any sort of ethics by limiting access to source code.

There’s an article that lists some of the issues developers have encountered. It also talks about teaching ethics as a way to reduce the impact, which is a good idea. But without any sort of accountability or transparency into the actual code, I’m not sure ethics will help much. There are always people that need a job, and may be willing to write code that continues to provide a paycheck because they aren’t sure they can get another job. I know most of us would like to think we wouldn’t succumb to pressure, but it can be hard to be sure what you’d do until you are confronted with a particular situation.

I do think this is a problem that will grow, and become more pervasive in the digital world, precisely because no one really knows what software is doing under the covers, and it’s hard to even determine what might be happening if you could access the source code. After all, can you be sure that code you get is actually what is running? You can, but not easily.

What I’d like to see is some sort of framework that allows us to specify the behaviors of our software, along with the data movement and handling so that end users would have an understanding of how the software should work. Maybe some automated way of producing a “contract” based on the code. Then we could resolve disputes without ever needing to examine the source.

Steve Jones

The Voice of the DBA Podcast

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

November 30, 2016
Finally, Create or Alter
There are lots of reasons to upgrade to SQL Server 2016, but this is the one for me. We finally get a CREATE OR ALTER statement in T-SQL. This not only makes lots of code easier to write, it means that the ways in which you might script and schedule your future deployments will be cleaner. This is an exciting change for implementing a simpler and easier Continuous Integration/Continuous Deployment system in your organization.

It’s not perfect news for a few reasons. First, this is a SQL Server 2016 addition to T-SQL only. That means until you have most of your applications have moved to SQL Server 2016 SP1+, you won’t be able to use this construct. That’s OK, because it will mean that at some point most of our instances will be on SQL Server 2016 SP1 or later, and much of our code will be cleaner. We won’t resort to including IF statements in our deployment scripts. We won’t need to create stubs of procedures and functions so our code is embedded in an ALTER script. In essence, you won’t need to maintain two separate code constructs to make a change.

This isn’t perfect, nor is it complete. We still don’t have CREATE OR ALTER for tables. That’s the place where I’d really like to get a consistent way of coding items. What I really want is a complete view of the table each time I change it. By this I mean that if I create a table like this:
```
CREATE TABLE Students
(
    studentname VARCHAR( 200),
    status TINYINT
);
```
Then I want to be able to add a column like this:
```
ALTER TABLE Students
(
    studentname VARCHAR( 200),
    status TINYINT,
    DOB DATE
);
```
Or alter a column like this:
```
ALTER TABLE Students
(
    studentname VARCHAR( 200),
    status BIT,
    DOB DATE
```
);

Or better yet, have a CREATE OR ALTER for tables.

I know this might be asking for a lot, but I really think that we ought to get a consistent way of coding databases so that we can reduce the mistakes and make our systems easier to understand. I’m sure this may require substantial engineering, not to mention a great deal of understanding of how this would actually affect our systems when run, but it would certainly make our code cleaner.

I doubt we’ll see these kinds of changes, at least not until we have an ANSI standard that encompasses them, but I would hope that as an industry we would mature and improve the way we work with databases, not remain bound by tradition and history.

Steve Jones
November 29, 2016
Rate a Session for GroupBy

One of the things that I struggle with is understanding whether a session at a conference like a SQL Saturday is worth watching. I also struggle writing abstracts and attracting people to my own sessions, so I think the idea of GroupBy allowing rating and reviewing of abstracts in advance is fantastic.

First, let me encourage many of you to go take a minute today and rate an abstract. If you see something that you like, leave a note. If you aren’t sure of something, or don’t like something, or even have a question about the content, leave that note as well.

I’ve been fortunate to attend lots of events. I speak at many, but I try to view a session or two at each as well. I’ve seen some great ones, and some poor ones. I do try to provide constructive criticism, and I do so privately. If you’d like feedback from me at any event, please ask.

One of the things I’ve seen is that the abstract often doesn’t quite match the talk, or the abstract doesn’t really help me understand what will be covered. It doesn’t matter if someone has tried to write a cute description or a plain boring one, the writing doesn’t always match the talk well.

I know I make mistakes in my abstracts. I know sometimes I write something that I realize later isn’t quite what I think will work in the talk. I’d like the chance to edit and correct small items. More importantly, I’d like to be sure that if my abstract topic (and talk) could slightly be tweaked in a way that more people like, I want to do it.

Take a minute and give some feedback. Be honest, rate what you want, don’t take up too much of your day, and help improve the conference schedule.

I’d love to see PASS implement this as well for the Summit. I realize this can be hard, but I would prefer to see some give and take in advance to help build the best set of sessions that people want to attend.

November 29, 2016