T-SQL Tuesday: Life hacks to make your day easier

SQL Tuesday time again. This time we are getting this invitation from no one less than Jess Pomfret, a SQL Blogger and Speaker with a website that reads “Jess Pomfret - SQL Server DBA Attempting to Blog”. I read many of her posts in the past and I’m happy to contribute to this challenge, hopefully with something interesting that can get people thinking about new ways of doing things.

Jess sent us this invitation and in a nutshell she wants us writing about productivity hacks we use daily to make our jobs more effective. Nothing fancy here, just things that work for us and add value. The topic is pretty open and that’s what I love about it: we ALL have this or that tip or idea that we can share.

My topic is looking for your favorite ‘life hack’, something you use to make your day easier

I liked Jess approach, so I copied it...

Some people post their own invitations with a promise to write their own posts later, but in this case, Jess added her own tips right there, in her invitation post. She also included some useful animated gifts “a-la-VScode-examples” so you can see her tips in action.

I decided to tackle my post using a similar approach. I will share my tips explaining what I do and showing it on a short series of animated GIFs.

The problem I have with Keyboard Shortcuts

I remember when I started doing this (doing this = working in IT). It was the time where Microsoft products (both OS and software) really matured in many areas and left many of the competition behind… about 25 years ago. Something very cool I saw back then (I think MS Word started it as far as I experienced) was the idea that Keyboard Shortcuts didn’t have to be a finite series of key combinations you had to memorized anymore. On one hand, by providing Keyboard Shortcut configurations to the tools they allowed the user to turn any action into a Keyboard shortcut (not just what came pre-conceived in the package) and, on the other hand, you can make that keyboard shortcut whatever you wanted.

Off course we all knew there were best practices. As a developer you knew that help should always be available by pressing F1 or that closing a window was ALT + F4, but when the potential to turn anything into a shortcut turn into a reality, then what was the “best practice” to for example “adding a top border” shortcut? how about “applying a subtitle formatting to a paragraph” shortcut?

Everyone started to define whatever THEIR best practice was and people put so much love and care into building their own environments the way it was comfortable for THEM that the need to export/import environment configurations became a basic feature all editors (SSMS, Notepad++, VSCode, and so on…) now have.

My problem with Keyboard Shortcuts is not in their usefulness but in the fact that, for me to really reap the benefits of them, I have to remember the exact key combination or, in other words, I have to use them all the time (maybe that’s why I never managed to learn VIM no matter how many tutorials I completed). Let me put it with an example: we all know that CTRL + B bolds text in most of rich text editors, but only a marginal set of that people know what is the exact key combination to “Center Text” or “Repeat your Last Command”.

So how do you manage to automate via shortcuts and not be limited to the same dozen or so actions that you do day in and day out. How do you remember actions that maybe you do once a day? maybe once every other day? once a week? or maybe once every six months?

And that’s how I started to use keyWORD shortcuts in addition to keyBOARD shortcuts

What is the difference between a keyWORD shortcut and a keyBOARD shortcut?

A Keyboard shortcut is what we discussed so far, a combination of 2 or 3 keys that do something and prevent you from having to type to much or move your keyboard to places where you don’t know exactly where they are (in order to create that effect/perform that action).

A Keyword shortcut is similar in the sense that it is a shortcut to an action, but what triggers that action is not a 2/3 key combination but typing a word.

Yes, is that simple, type and word and get something back…

Why it is better in some cases? for 2 main reasons

Keyboard shortcuts eventually run out and overlap: some apps use a combination for one thing and others for other things. For example, I have a translator (Babylon) that pops up every time I’m debugging code because F10 opens it up (when I’m trying to “step through” on my debugger)

Keyboard shortcuts are not easy to remember: yes.. I know if you do something day in and day out, you will likely remember some obscure shortcut for it, but then the question is this: is there any way the brain can associate those key combinations with the action other than repetition and brute force?

Here is the alternative: If that action is to write the template of a CREATE TABLE how about a Keyword called sqlnewtable?

Even if you don’t do that all the time, as long as you create strict rules for naming your keywords, you can probably guess your keyword right in a 99.9% of cases.

How I do it

Easy, four main rules

• All my t-sql templates start with sql, all my email templates start with eml but you can use whatever naming convention you want (just as with shortcuts)

• I have other standards like trying to also refer to tables as “tbl” (so my shortcuts are shorter to type)

• I have a Snippet Library that provides keyword shortcuts (called “auto text” in this tool but is the same basic idea). The one I have installed was built by a company that I believe is out of business (calling OverZone software) so I can’t recommend that anymore, but I’m sure other similar tools provide that functionality

• When the target of my keyword shortcut provides token replacement (such as the case with SSMS) then I also take advantage of that by adding TOKENS in the text that gets generated with my keyboard combination. For example, I have a key called sqldroptblifexist that displays this T-SQL

And yes… I use a Keyboard Shortcut for that final step (CTRL+SHIFT+M). This is to emphasize the fact that I have not entirely replaced Keyboard Shortcuts at all, I still use them in many cases (I would not bold a text in any other way that was not CTRL+B). Keyword shortcuts are not meant to replace Keyboard Shortcuts, they are meant to be effective in cases where Keyboard Shortcuts are not.

Some examples in action…

Enough talking and let’s see how this whole thing fits together. Notice how in all the animations start with some text magically appearing on the screen (T-SQL or emails in these cases). This is not a COPY/PASTE operation, this is me invoking my text by using keywords.

Two T-SQL scripts that I run every now and then that helps me to figure out what is the best size to shrink my DB (keyboard shortcut = sqlshrink).

Several sample calls to sp_DBPermissions(keyboard shortcut = sqldbperm)...

A T-SQL script to look at the backup history of a database (keyboard shortcut = sqlbackuphistory)...

Another very handy script to calculate the best Max Degree of Parallelism for a server and to see MAXDOP metadata (keyboard shortcut = sqlmaxdop)...

Writing an email to notify people about a cleanup event I do monthly (keyboard shortcut = emldbcleanup)...

The possibilities are endless, use keyword shortcuts to navigate to web pages, open up directories in your network… anything goes!

Good luck automating with keyword shortcuts!