THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

SQL Formatting Matters…

… to some folks more than others.

We likely all have stories about working with folks who care a lot about the format of their SQL statements. I’ve worked with a lot of database developers and analysts. I cannot say SQL formatting distinguishes the good from the bad (or ugly), but I have noticed the sharpest developers have a preferred SQL format. I’ve also noticed the very sharpest are the most dogmatic about the format they prefer.

Why is this?

I don’t know for sure. I’ve asked, and a few shared that their preferred SQL format helps them visualize or conceptualize the purpose of the statement. I can buy that. When working with folks who have a SQL format preference, I try to only send them SQL formatted as they like.

How about you? Does format matter? If so, why?

:{>

Learn more: 
Stairway to Biml
Linchpin People Blog: SSIS
Stairway to Integration Services

SSIS Design Patterns

Published Monday, July 20, 2015 1:43 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

FormatRolled said:

SELECT, FROM, WHERE, all belong on their own line.

In fact all major clauses belong on their own line.

Each table in the FROM clause belongs on it's own line, as does each join condition.

SELECT A.NeverGoing, B.ToGive, C.YouUp

FROM Table1 A

    JOIN

    Table2 B ON A.ID = B.FK_ID

            AND A.B_ID= B.ID

    JOIN

    Table3 C on A.C_Id = C.ID

WHERE A.CelebrityName = 'Rick Astley'

July 20, 2015 1:28 PM
 

Bob Probst said:

Formatting definitely matters but only insofar as it is readable and supports swift debugging.  I have several things that I prefer when I write it but when I'm faced with badly formatted code, the default IDE formatter will get me to my comfort level.

Things that I'm very picky about:  

parens around my table join conditions

alias first in my selects (ie:  total = sum(total) not sum(total) as total)

use table aliases everywhere or nowhere

After that, I'm pretty democratic about formatting.

July 20, 2015 1:57 PM
 

Patrick Cahill said:

I am very picky how my sql is formatted. By setting up the desired format in SQL Prompt it does not take any extra time to have it formatted the same every time. The reason I want it formatted the same is it makes it easier to read. It also makes it easier to pick out parts that do not meet our coding standards.

July 20, 2015 2:02 PM
 

Richard Back said:

For me, its good to have a standard for SQL formatting that is adopted consistently: just like for any other code. Its less important what that standard is- but if everyone in a company or team uses the same formatting, it makes it quicker and easier to understand code when debugging or improving it.

If you have a preferred format that you're dogmatic about, its probably because you've thought long and hard about what the format should be- and probably only the sharpest SQL coders care enough to think that long and hard about what makes sense to them. On the other hand, I'd have thought the 'top top' coders would be fairly tolerant of other formatting methods- if you can see the code like Neo you probably don't care where the tabs are.

July 20, 2015 2:09 PM
 

Yaroslav said:

Yeap, for me it matter, a lot. Maybe for a short and small sentence, without joins or a where clause, I can live with it. But is not the same to deal with a query that expands to the infinity and beyond, several lines, lot of complex joins, subqueries everywhere, cases clauses and more. To get a better/quick understanding of those queries it do matters a lot if it's correctly formatted. If it comes from one of our devs and it is not formatted using our current standards, is not so bad, as long as it is formatted somehow and helps understanding what is going on there.

July 20, 2015 2:12 PM
 

Peter Schott said:

It matters because of the way I parse the code. However, it matters less when you have all sorts of tools that can re-format it for you. :) I really like Red-Gate's SQL Prompt, but have been playing with ApexSQL's Complete and Refactor lately as viable alternatives while trying to work out what would need to be licensed and where.

July 20, 2015 2:20 PM
 

Scott D said:

@FormatRolled, I agree with the first two lines. Maybe because I haven't had to deal with anything super-complex, I prefer this layout:

SELECT A.NeverGoing, B.ToGive, C.YouUp

FROM Table1 A

 JOIN Table2 B ON (A.ID = B.FK_ID) AND (A.B_ID= B.ID)

 JOIN Table3 C ON A.C_Id = C.ID

WHERE A.CelebrityName = 'Rick Astley'

I also prefer using double-space for indenting rather than tabs. If you're copy-pasting code between applications, you can end up with the layout being mucked up if the different applications have different spacing for tab characters. If the number of columns being selected is too long, I will put each column on its own line as well:

SELECT A.NeverGoing,

      B.ToGive,

      C.YouUp

FROM Table1 A

 JOIN Table2 B ON (A.ID = B.FK_ID) AND (A.B_ID= B.ID)

 JOIN Table3 C ON A.C_Id = C.ID

WHERE A.CelebrityName = 'Rick Astley'

Some people prefer the commas at the start of each line, and I can see the benefit, but for some reason I still prefer them at the end.

July 20, 2015 3:31 PM
 

Ben Thul said:

If it matters that much to you (and that's not a judgement), you'll teach a computer how to do it (e.g. SQL Prompt, et al).

July 20, 2015 5:01 PM
 

RichB said:

I quite like everyone to do it their own way.  Let's me see quite easily who it was ***ed around with a bit of code and didn't put it in source control...

July 20, 2015 9:57 PM
 

Mark Wojciechowicz said:

Code formatting reduces defect density, for all the reasons noted above. Understanding the code is easier and so code maintenance is easier and mistakes become more obvious.

July 21, 2015 9:26 AM
 

Drewsky said:

Formatting is important.  When I notice I'm getting a bit messy (or if I inherit some ugly SQL), I head on over to this web page:  http://www.dpriver.com/pp/sqlformat.htm  .  Plenty of options and similar to SQL prompt output.  They also format MDX!

July 21, 2015 11:05 AM
 

Shauna Ayers said:

Debugging well-formatted SQL can be at LEAST 10-20% faster than debugging the same code when poorly formatted,  I've found.

July 21, 2015 11:35 AM
 

Kevin Stevens said:

For anyone who has had to make changes to a sql variable inside of a SSIS expression builder knows that the easiest way to modify the code is to copy and paste it into SSMS and then work with the code inside of SSMS.  Visually the readability of the code is what makes it easier to understand.

July 23, 2015 7:48 AM
 

John Trollope said:

Formatting matters a lot to me as it serves as a quick way of parsing the code visually. I find that with a rigid structure, any deviances tend to be problem areas (perhaps as simple as the missing "end" from the if statement). I personally don't mind what pattern a developer uses as long as it is consistent!

October 6, 2015 1:57 PM
 

Lakshman said:

Yes, definitely it matters.

formatting is for user-friendly/ human readable.

May 14, 2016 9:26 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement