SQL Time-Savers: A Few Nifty Tips to Ease Your Querying
In my current role at WSC Sports, I spend a fair portion of my time writing SQL queries in Microsoft SQL Server Management Studio(SSMS).
Within my first few days in the company, I quickly noticed how repetitive SQL querying could become. I remember thinking to myself one day: “If I had a dollar for every time I typed SELECT * FROM, I’d certainly have enough money to hire someone to do this for me.”
This led me in the pursuit of finding solutions to these repetitive tasks so that I would be able to pivot more of my time from ‘menial querying labour’ into logical and analytical problem solving.
Here are just a few of my favourite tried and tested time-savers that contribute to saving me hours every week:
1. dbForge SQL Complete (Express)
The SQL Complete free add-in for SSMS is, in my opinion, so vital, that I cringe at the idea of coding without it. When tables become plentiful, and columns are a-many, the auto-complete feature is a life-saving time-saver. Though there are many more great features in the paid version, the feature I’d like to focus on, which is also available in the free version, is the ‘context-sensitive suggestion’ feature that creates and alters suggestions as you type. From keywords, to columns and tables, this speeds up your querying substantially.
The camel-case predictor is particularly great for those of you that save your tables and columns in camel-case format (which I believe you all should).
2. ApexSQL Complete
The perfect complimenting add-in to the dbForge complete is ApexSQL.
You may be asking “Why do I need both Apex and dbForge?”, which is a fair question. The answer is that the specific uses of these two add-in are disjoint.
As it stands today, the Apex auto-complete is not as good as the dbForge one, it lacks the camel-case capability, has an inconvenient load time, and is less user friendly. What Apex does have, however (which SQL Complete only has in the paid version) is its auto-replacement feature, highlighted and recommended by my good colleague, Michael.
Once downloaded, ensure that Apex auto-complete is disabled so that it does not conflict with dbForge, and that auto-replacements is enabled.
Next, in the ApexSQL menu, Select ‘Manage auto-replacements…’ . Here you can define custom aliases to the intricacies of your heart’s content.
My personal favourite is aliasing ssf to be SELECT * FROM as shown above.
The amount of time this single three letter alias has saved me should not be underestimated. Indulge me while I nerd out for a moment:
Let’s assume that it takes me 3 seconds to type out SELECT * FROM and 0.5 seconds to type ssf . That’s a 2.5 second saving, modestly, 100 times a day, 5 days a week for a year. That equates to over 18 hours. Put that in your pipe and smoke it!
3. QueryVariables table
If you have a file that has multiple queries, all using the same set of variables multiple times throughout, you may find yourself often having to go through the file repeatedly changing the value of the variables every time you use the queries.
A solution I found to this problem is creating something that equates to global variables. You would only need to set the variables once, after which your queries are set and ready to be executed.
To do this we create a table and name it something like QueryVariables, GlobalVariables or QV. The table should consist of only two columns, namely [Key] and [Value].
Here is the query to create the table:
Using this table is similar to using the DECLARE @local_variable syntax, only that the variable remains set beyond the end of your query. This is beneficial if you want to ping-pong between the queries individually instead of executing them all simultaneously.
Example
Instead of having these three queries:
You could have this:
Once your files grow to substantial sizes and you have multiple variables, this variables table becomes invaluable. If there is more than one of you using this table, I suggest defining user-unique keys as to avoid updating one another’s variables.
4. Using Shortcuts
When your goal is to save time, your keyboard is your best friend. Familiarising yourself with the shortcuts of your software and training yourself to make use of them may seem like a bother in the short run but your time-based ROI makes this tweak to your querying a no-brainer.
Here is a full list of keyboard shortcuts for:
SQL Server Management
dbForge SQL Complete
Apex SQL
I still have a lot of shortcuts to incorporate in my daily querying but here are a few that I make use of extremely often:
- CTRL + SPACE
Open the suggestion box - CTRL + K, CTRL + E
Enables execution of the current statement - CTRL + K, CTRL + C
Make the selected text a comment - CTRL + K, CTRL + U
Uncomment the selected text - CTRL + Left/Right Arrow
Cursor skip words instead of characters (if you hold shift it highlights the words for easy deletion) - ALT + SHIFT + Up/Down Arrow
Vertical selection or multiple line edit (shown below)
Finally
In the, somewhat gender biased, words of Robert Heinlein:
“Progress is made by lazy men looking for easier ways to do things.”
If there are any other ‘lazy’ SQL’ers out there with some points to add, questions to ask, or comments to make, I invite your input and hope to publish a version 2.0 of this post in the future.
Happy coding.
WSC Sports’ Cloud-Based Platform
Our platform generates personalised sports videos for every digital platform and every sports fan — automatically and in real-time.
Currently being used by leading media rights owners such as NBA, MLS, FIBA, Cricket Australia, WarnerMedia, Discovery, Amazon, Bleacher Report and many others, WSC Sports’ platform utilises advanced AI and Machine Learning technologies to analyse live sports broadcasts, identify each and every event that occurs in the game, create customised short-form video content and publish to any digital destination. This enables partners to instantly generate and distribute professionally edited personalised clips and videos on a large scale, to engage audiences and maximise video monetisation opportunities.
Our platform is cloud based meaning users can work from wherever they are with access to all of their content at any time.
Find out more about WSC Sports here: https://wsc-sports.com
