How To Write Better SQL Queries

How To Write Better SQL Queries

Howto, Technology
Play

In this audio session we talk about how strategic query formation can help you get exactly the information you’re looking for with a minimal amount of work

Here’s a video of the technique I describe in the recording

Key points from this lesson:

  1. Start with the end in mind
  2. Alias the tables
  3. Let SSMS do the heavy lifting

Start With The End In Mind

What is the main body of information we wish to find?  Where does that information live in the database?  This is the table we’re going to query first.  All other data we use to enhance the dataset will appear in the form of joins, subqueries, and all the usual suspects.  Starting with the FROM portion of the query will get things set up so that you are going straight to the key information.

Alias The Tables

If you’re typing out [myserver].[mydatabase].[dbo].[myreallylongtablename] every time… well really – why are you doing that?  I can’t think of any good reason.  Even when querying a linked server, type it once, alias it, and get out of the typing business.  We alias a lot of the tables – sometimes even all of them, if we can do it and maintain readability of the code — to get the query built quickly, and to make it more readable.  If I can read 3 characters and understand it as well as if it had been spelled out completely, I”m sold.  Aliases are great.

Let SSMS Do the Heavy Lifting

Once you’ve started with the FROM line and aliased the main table, anything you type into the SELECT section will have a much stronger chance of being spoon-fed to you by SQL Server Management Studio (SSMS).  The less typing we do on our own, the less we have to go back and troubleshoot typos.  Less work, less troubleshooting.. win, win.

That’s all there is to it.  This is one of my favorite techniques when working with SQL Server Management Studio.  It makes every day easier for me, and I hope it will help you too.  In this clip we just touched on the topic.  If you have a question, please comment in the comment section below and I’ll get back to you with an answer.