5 ways to write SQL faster

When I sit down to work, I always want to be as productive with my time as I possibly can and I’m always looking for ways to improve. Since I spend the majority of my time in SQL Server, many of the little boosts to my productivity are related to how I can write SQL queries faster. In this article, I want to share some of the things I’ve learned that I hope will inspire you to look for ways to optimize your own workflow. 

1. Get an add-in for automatic formatting, snippets, and advanced intellisense. 

Depending on where you work this may not be an option, but if it is, definitely get an add-in for SSMS that provides automatic formatting, snippets, and advanced intellisense. Personally, I use SQL Complete by Devart. I can’t begin to express how much time this can save you in the long run. Let’s take a quick look at each of those features. 

Automatic formatting

Let’s face it, everyone has different opinions about how you should format SQL. The compiler doesn’t care at all how much white space you have and, as a result, you can have wildly different looking code that does the same thing. The issue is worse when you are working with other developers and analysts who prefer a drastically different style than you. 

Some companies implement guidelines as to how you should format your SQL to promote consistency and readability between coders, but there are significant downsides to this as well. You may not like the way the guidelines specify and it’s annoying to reformat your code to meet someone else’s idea of what code should look like. 

Automatic formatting can help with this problem. With an add-in like SQL Complete, you can set up profiles of how you like your SQL formatted and with a press of a button it will make it happen. Since you can set up as many profiles as you want, you can have multiple layouts for how you or others prefer their code. When you get code from someone else, you can quickly modify it to look more like what you’re accustomed to. When you’re ready to send it back to them, you can switch it back to they way they like it. 

I personally have 4 different profiles that I will use for my own code depending on the situation. Sometimes you want your code very compact so you can see a long query as a whole and sometimes you want to expand it out to understand a specific part. I am always switching between profiles depending on the situation. 

Snippets 

Snippets are wonderful. Let’s say you have a table called “NonciliatedPseudostratifiedColumnarEpithelium” that you have to use in almost every query you write. Assign that joker to “npce” so you can type 4 letters and press TAB to expand it out. It will change your life. 

Obviously you can use snippets for more than just abbreviating table names. I have one called “try” that inserts the skeleton of a transaction with TRY…CATCH blocks with the error handling techniques I usually use. I don’t have to think about that stuff every time, I just type “try” and it’s ready to roll… or maybe rollback :) 

Intellisense 

Relevant information at your fingertips is always helpful. Many add-ins like SQL Complete or Redgate have intellisense that gives you information more quickly than you could get otherwise. One of the ones I enjoy the most is the ability to hover over a table name and see what the columns/data types are.

2. Get a gaming keypad for shortcuts.

A gaming keyboard?? At work??

Hear me out on this one. I have a Logitech G13 Gameboard sitting on my desk to the left of my keyboard and it has exponentially improved my productivity. This guy has 24 programmable buttons that, with Logitech’s gaming software, you can program to do all kinds of things that will save you an unbelievable amount of time. If 24 buttons aren’t enough for you, there are 3 profiles you can switch between to get even more. Here are just a few of the things I do with mine. 

• Switch between SQL Complete formatting profiles (combine time saving tricks for bonus points!!)
• Run the SQL Complete formatting profile on the entire document or just the selected text
• Increase or decrease indentation
• Make the selection uppercase or lowercase
• Change the Server Connection or expand the database selection menu
• Switch between my custom light/dark themes 

There are a lot of buttons to remember, but trust me, it’s worth it. People think I’m nuts when they see this thing sitting on my desk, but they can’t argue with how fast you can get stuff done with it. 

3. Get a mouse with ton of buttons 

OK, this is much along the same vein as the previous suggestion. I have a Logitech G602 Gaming mouse that has 8 extra buttons on it that you can program just like the keypad. This is useful for quick commands that you use all the time. 

This is especially handy for me because I use Dvorak, which is an alternative keyboard layout that is designed to be as efficient as possible. As nice as it is to type in Dvorak, you do lose a little bit of convenience when it comes to shortcuts. Ctrl + C and Ctrl + V are nowhere near as easy to reach as in QWERTY and you can’t do it with one hand. Trust me, this is a MAJOR issue. Fortunately, I just assigned the Ctrl + C and Ctrl + V key combinations to two of the buttons on this mouse and now I can copy and paste faster than ever! 

4. Use templates 

I don’t know how many people use this, but I really like using the template feature in SSMS. I have all the ultra-helpful queries I’ve written set up as templates that I can open quickly without fear of accidentally overwriting the original. One of the ones I use all the time is a query to list all the columns in a database that that match a particular string. When you have databases with 300+ tables, it can be a real pain to track down which one has the data you are looking for. Templates are the way to go.

5. Make a custom dark theme 

Ok, I’ll admit it, this one doesn’t actually make me any more efficient, but it sure makes my eyes happier. If you’re a developer accustomed to to the dark theme in Visual Studio, you might be annoyed to discover that SSMS doesn’t have a dark theme option. Fortunately, you can change the color for specific things in the query window. With a little bit of tweaking, you can set up your own custom theme. Then you can save the settings so you can easily import them when you want them. If you also have programmable buttons, you can assign two of them to switch between your light and dark themes. It is legit. 

Conclusion

There are lots of other things you can do to be more productive, but these are a few of the ones that I really enjoy. If you have some tricks or tips you like to use, I’d love to hear from you! 

You may be wondering why I haven’t mentioned AutoHotKeys. I requested permission to install AutoHotKeys on my system at work and was turned down, so the gamepad option worked out really well as an alternative. Pretty much everything I say about the gamepad you could program with AutoHotKeys and not actually need a separate keyboard for it. For me though, I prefer it this way anyway.