… 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
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’
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.
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.
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.
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.
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.
@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.
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).
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…
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.
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.
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!
Debugging well-formatted SQL can be at LEAST 10-20% faster than debugging the same code when poorly formatted, I’ve found.
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.
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!
Yes, definitely it matters.
formatting is for user-friendly/ human readable.