Thu 12 Mar 2009
Simon Sabin, fellow SQL MVP, has posted on the topic of formatting SQL.
For me, I align my queries vertically like so:
select track,
level,
title,
Name,
ss.length
from ConferenceSession cs
join session ss
on ss.sessionId = cs.SessionId
join Speaker sp
on sp.SpeakerId = ss.ownerId
where cs.Approved = 1
and cs.ConferenceId = 4
order by length,
title,
cs.SessionId descIf I have multiple join predicates, I continue the indention pattern:
select track,
level,
title,
Name,
ss.length
from ConferenceSession cs
join session ss
on ss.sessionId = cs.SessionId
and (ss.approved = 1
or ss.track = 'DBA')
join Speaker sp
on sp.SpeakerId = ss.ownerId
where cs.Approved = 1
and cs.ConferenceId = 4
order by length,
title,
cs.SessionId desc
What is your SQL formatting style?
March 12th, 2009 at 3:36 pm
I prefer include assing a row for parenthesis
on ss.sessionId = cs.SessionId and
(
ss.approved = 1 or
ss.track = ‘DBA’
)
Regards,
March 13th, 2009 at 7:15 am
I like things much more stretched out then most people… Everything essentially gets its’ own line with all columns and / or logic conditions going first on the line (instead of last on the previous line). All joins are broken up with a double line break to make it easy to pick out where the next source is coming from.
SELECT
TableA.ColA
, TableA.ColB
, TableB.ColC
, TableB.ColD
, TableC.ColE
FROM
TableA
INNER JOIN
TableB
On
TableA.B_Key = TableB.B_Key
INNER JOIN
TableC
On
TableA.C_Key = TableC.C_Key
WHERE
TableA.ColA > RandomValue
AND
(
TableB.ColG = OtherRandomValue
OR TableC.ColH is null
)
ORDER BY
TableA.ColA
, TableB.ColC
March 13th, 2009 at 7:16 am
Grr… Web formatting… There should be a single space on the lines that are not key word (SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY) as indentation. I usually use a tab on the lines that are wrapped in parenthesis.
March 14th, 2009 at 10:23 pm
Do you use a specific tool to format your SQL?
March 15th, 2009 at 9:18 pm
TextPad is pretty much all I use to format anything I write.
June 2nd, 2009 at 11:12 am
I have a similar style but I put the keywords in capitals.
How do you do when you specify outer joins? There doesn’t seem to be room enough on the left side.
June 2nd, 2009 at 11:53 am
Andreas,
The keyword “OUTER” is optional, so what I do is keep “left” or “right” on the left side of the indentions, like so:
select track, level, title, Name, ss.length from ConferenceSession cs join session ss on ss.sessionId = cs.SessionId and (ss.approved = 1 or ss.track = 'DBA') left join Speaker sp on sp.SpeakerId = ss.ownerId where cs.Approved = 1 and cs.ConferenceId = 4 order by length, title, cs.SessionId descJune 3rd, 2009 at 1:24 pm
Nice, I think that I’ll copy that style.
June 17th, 2009 at 12:38 pm
{my 2c worth}
I do a few minor things differently. For example, I use leading commas in my SELECT list.
This also puts a space between the comma and the column name which allows you to select just the column name with double-click in SSMS.
The aliases are all vertically aligned - makes it easy to glance down a “column” to see where things are in a query for me.
In the case of the nested JOIN criteria, I start to vertically align within the “block” created by the enclosing parenthesis - but I try to keep those aligned with the outer verticle alignments. Admittedly, that can sometimes cause confusion.
I also add comments so if I’m not the one looking a query, whoever is next at least knows SOME of what I did.
If I have to use hard-coded values, I put those in variables and declare and describe them briefly, but try to give at least some info to anyone “following”.
Finally, all of the keywords like SELECT, FROM, WHERE, JOIN, etc are upper-cased. It’s just a personal preference because my column names often are proper or lower. So it helps me in distinguishing keywords from columns.
Here is the example query formatted as described:
———————————————————
– Variable Declaration
———————————————————
DECLARE @SessionApproved BIT
, @ConfSessionApproved BIT
, @ConferenceID TINYINT
, @ConfTrack VARCHAR( 5 )
———————————————————
– Variable assignments
———————————————————
SET @SessionApproved = 1 — 0 = Not approved, 1 = approved. References [session]
, @ConfSessionApproved = 1 — 0 = Not approved, 1 = approved. References [ConferenceSession]
, @ConferenceID = 4 — Unique ID for this conference. References [ConferenceSession]
, @ConfTrack = ‘DBA’ — Text abbreviation of the “track”. References [session]
———————————————————
– Resultset Generated here
———————————————————
SELECT track
, [level]
, title
, [Name]
, ss.length
FROM ConferenceSession cs
JOIN session ss
ON ss.sessionId = cs.SessionId
AND ( ss.approved = @SessionApproved
OR ss.track = @ConfTrack )
LEFT
JOIN Speaker sp
ON sp.SpeakerId = ss.ownerId
WHERE cs.Approved = @ConfSessionApproved
AND cs.ConferenceId = @ConferenceID
ORDER BY
length
, title
, cs.SessionId DESC
Whatever your preference, I think the most important thing is consistency. If you’re consistent - your methods can be horrible, but at least anyone else looking at the code will have a chance of knowing what to find each time they look at a new query.
{end my 2c worth}