In Late July 2022, Brent Ozar charged the SQL Server community to Make September 2022 “Free Community Tools Awareness Month”.
The idea behind the initiative is that no one should have to re-invent the wheel–so if we as data professionals share with one another some (free) tools and knowledge that have helped us along the way, we’ll all be better off.
Brent’s instructions are simple. From his post:
Pick one of these things to share:
- Introduce the tool to readers for the first time
- Tell a story about how it saved your bacon
- Share a non-default configuration option that you use, and why
- Write a review – explain what you like about a tool and what you wish was different
- Compare several free tools that do the same thing – explain the pros & cons of each one
- Put together a list of learning resources for a free tool – maybe you like the tool, but it isn’t easy to use, and you want to put together a set of links to show a new user where to begin
Equally as simple is feature I’ve chosen to write about (from the area I’ve bolded above). I realize a non-default configuration isn’t a tool per se, but hey, it’s allowed–and there’s one configuration I set every single time I install SQL Server Management Studio (SSMS). And for something I set and use so consistently, I have to imagine there are others who might also benefit from this configuration/feature: Custom Query Shortcuts.
Custom Query Shortcuts
There are plenty of shortcuts built into SSMS–and they’re great. They save users a significant amount of time over years of working with T-SQL and SQL Server. However, there’s not a built-in shortcut for everything, and since we all have our own coding styles and preferences, sometimes a bit of customization goes a long way. This is where Custom Query Shortcuts shine.
In SSMS, if you click on the “Tools” menu and then select “Options…”, you’ll be presented with a window similar to the following.
The “Query Shortcuts” section is located under “Environment–>Keyboard”. In this section, there a few pre-defined shortcuts that cannot be changed–namely “Alt+F1” for sp_help, “Ctrl+1” for sp_who and “Ctrl+2” for sp_lock. Outside of those, you have nine other shortcut keys to which you can assign commands.
The way these work is that, when a given key combination is pressed in a query window, the associated command will get executed. Furthermore, if you have any text highlighted when you press the key combo, that text will get appended to the end of the command.
While I typically define several custom query shortcuts, I want to focus on the two I find most useful:
Ctrl+3: “SELECT TOP 100 * FROM “
Ctrl+4: “SELECT RecordCount = FORMAT(COUNT(*), ‘N0’) FROM “
Note: There is a <space> after “FROM” in each of these commands.
As you can see, the first shortcut/query allows me to get a sample of records from a table I have highlighted. The second allows me to get a nicely-formatted record count for a table I have highlighted.
These come in handy anytime you have an existing query and are seeking quick access to a data sample or record counts without altering the query. The existing query can be anything from a SELECT query with multiple joins/multiple fields listed, etc. or an action query (i.e., INSERT/UPDATE/DELETE).
Consider the following screenshot where I want to see a sample of what’s in the StackOverflow Posts table without having to butcher this query or start a new query. I simply highlight (or double-click) on the word “Posts” and hit “Ctrl+3”.
Let’s say I want to know how many rows this entire query will return. Again, I could put my cursor at the end of line 4, hit Enter, type “SELECT COUNT(*)” on the new line, then highlight and run that line and the remaining lines of the query, then “Ctrl+Z” (undo) it all. Or, I could just highlight “Users” through the end of the query and hit “Ctrl+4”. Easy-peasy.
Not to mention the number is nicely formatted with comma separators and the fact that it’s much more efficient to leverage COUNT() than to just run the entire query to see how many records are returned–because SQL Server will use the narrowest index it can find to return an accurate row count.
Your Turn
While these specific queries are quite simple, they’re extremely useful for me and I use these shortcuts on an almost daily basis. I encourage you to think more broadly about what queries or stored procedures you find yourself using most frequently and see if it makes sense to incorporate them into custom query shortcuts (Spoiler: You’ll need to restart SSMS).
As you begin to use the shortcuts over doing things the original way, don’t be surprised if you start to wonder how you lived so long without leveraging such a simple, yet useful feature.