SQL Homework – March 2022 – Formatting

Leave a comment

March 1, 2022 by Kenneth Fisher

Please write the following on the chalk board 100 times.

Queries should be readable!
Queries should be readable!
Queries should be readable!
Queries should be readable!

The top three things, that, in my opinion, you should be paying attention to when writing a query are as follows.

  1. The query returns the data you expect.
  2. The query returns data in a timely manner.
  3. The query is understandable and can be easily maintained.

Take a piece of code you’ve written in the past. Preferably one that is moderately complicated. Say at least a page or so long with multiple queries and maybe some variables. Now, go through it and carefully format it. I don’t care how you format it, just be consistent. If keywords are capitalized, make sure they are always capitalized. Are variables capital letter first? Then they should always have the first letter capitalized. SELECT, FROM, and WHERE on new lines? Etc. Also include every best practice you can think of. Table names in the FROM clause should include the schema name, column names in the SELECT clause should be fully qualified, etc.

SELECT TableName1.Col1, TableName1.Col2, TableName1.Col3
FROM dbo.TableName1
JOIN dbo.TableName2
     ON TableName1.Col1 = TableName2.Col1
WHERE TableName2.Col5 = 'Yup'
   AND TableName2.Col437 = 'Nope';

Now that you’ve got a formatted piece of code, write down what your rules were. Then go back and make sure you followed them all the way through. Then make sure you didn’t miss any rules you want to keep, then back to the code again, etc. Back and forth a few times until you are pretty sure you have the formatting rules you want to use going forward.

Now here is the truly important part. For the rest of the month make sure that you follow those rules carefully! If you forget, then make sure to go back and fix it up as soon as you can. The idea here is to get into a habit.

Disclaimer: If your company has a format that they prefer (and some companies do) then use that format. If your company doesn’t, then maybe discuss it with your team and come up with a consistent format you can all use. Having 14 different formats in the same team is, maybe not worse than no format, but still pretty bad. Oh, and don’t format someone else’s code without talking to them first. People get annoyed about that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: