Written by The other day I was complaining on twitter about a almost 2000 character wide SQL Insert statement that I had to debug. Enough with the complaining, I'd like to show how I format SQL statments in my code. In the comments area I'd like to see how you format your SQL.
Break up Select, Insert and Update
Nothing is harder to read and debug than:
Insert Into NotNormalizedTbl (firstname, lastname, address1, address2, is_dog_lover, has_dog, dog_breed) Values ( 'Jon', 'Dowdle', '4000 Central Florida Blvd', true, true, 'mutt');
Even though this is a simple statement I find it takes a while to identify which column is missing.
Here is the same example but with each column and value on its own line.
Insert Into NotNormalizedTbl
(
firstname,
lastname,
address1,
address2,
is_dog_lover,
has_dog,
dog_breed
) Values (
'Jon', /* firstname */
'Dowdle', /* lastname */
'4000 Central Florida Blvd', /* address1 */
true, /* is_dog_lover */
true, /* has_dog */
'mutt' /* dog_breed */
);
Also, separate your Where conditions. Where a = 1 And b = 2 And c = 3 And.. isn't very easy to read. For the Where clause I like to keep each condition on its own line and indent each rule within the clause. This brings me to my next rule.
Indent
Indentation is next to Godliness, or so they say. My personal style is to indent as often as possible.
Consider:
Select * From table1 Inner Join table2 ON table2.id = table1.two_id
vs
Select *
From table1
Inner Join table2
On table2.id = table1.two_id
And table2.name = table1.name
Capitalize keywords
I know this sounds counter intuitive but let me explain the logic behind only capitalizing the first letter of keywords.
The reason for this is that the human eye picks up on the Capitalized Keywords while the ALL UPPERCASE WORDS are skimmed over (Proctor, R. & Vu, K. Handbook of Human Factors in Web Design. New Jersey: Lawrence Erlbaum Inc.).
Consider:
SELECT vs Select
Separate each clause
Don't leave items on the same line if they are from a different clause. While this provides better readability it also has a bonus benefit: commenting out a rule is a snap. This is best shown in an example.
Select * From table1 Where c1 <> "" and c2 is not null
vs
Select *
From table1
Where c1 <> ""
and c2 is not null
Comment
Just as with any code, the next guy/gal to read it might not be you. So when you're making the world's longest and most convoluted SQL statement, please comment what each part is doing and why.
Wrap up
To format existing queries I recommend using SQLInform (http://www.sqlinform.com/). It has a ton of options and has worked very well for me in the past.
Using these styles doesn't take very long to get used to, so give them a shot. The next time you debug a SQL script you will thank yourself. Once again, leave any tips or practices that you find helpful to keeping your SQL clean and maintainable in the comments area.