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}
March 7th, 2010 at 5:40 pm
I have been using something very close to Phil’s style (including
omission of OUTER) for over a decade now. Tried several
approaches before this, but haven’t found anything better. I would not
force it on anyone else, but I’ve noticed that over the years this has
brushed off on a few colleagues.
What I do enforce are documented standards for clarity, breaking up
non-trivial lists into separate lines, etc. But if colleagues
want to left justify keywords, that’s ok.
I can’t determine your exact spacing, but my formatting has the second
column (next word after SELECT, JOIN, ON, WHERE, etc) starting 2 spaces
after end of SELECT. This allows me to use std tab to indent to
this second column.
SELECT CONVERT(CHAR(10), oh.OrderDate, 120) AS OrderDate, oh.OrderID, ohs.StatusName, SUM(ol.SalesAmt) AS SalesAmtTotal FROM dbo.Table1 oh JOIN dbo.Table2 ol ON ol.OrderID = oh.OrderID LEFT JOIN dbo.Table3 ohs ON ohs.StatusCode = oh.OrderStatusCode WHERE oh.CustomerID = @CustomerID GROUP BY CONVERT(CHAR(10), oh.OrderDate, 120), oh.OrderID ORDER BY CONVERT(CHAR(10), oh.OrderDate, 120), oh.OrderID
Since many of my colleagues use small/different tab settings, I use
Textpad’s “convert existing tabs to spaces when saving files” feature,
so resulting code is correctly formatted regardless of tab settings.
I too use Textpad for most of my work, purchased it over a decade
ago. Someday I’ll probably switch to an Intellisense-based tool
for coding SQL, but so far I haven’t found anything yet that makes me
faster. I probably spend 10-20hrs/year checking for new tools
like this.
Recently met Eric Wisdahl in an Ybor City pub after Tampa SQL
Saturday. Great guy. Smart guy. I personally don’t
stretch out code as much as his
example above, but at least I could readily maintain his code w/out
having to first reformat it for understanding.
March 7th, 2010 at 5:43 pm
Grr…web formatting. Would be great if prior post could be dropped.
I have been using something very close to Phil’s style (including
omission of OUTER) for over a decade now. Tried several
approaches before this, but haven’t found anything better. I would not
force it on anyone else, but I’ve noticed that over the years this has
brushed off on a few colleagues.
What I do enforce are documented standards for clarity, breaking up
non-trivial lists into separate lines, etc. But if colleagues
want to left justify keywords, that’s ok.
I can’t determine your exact spacing, but my formatting has the second
column (next word after SELECT, JOIN, ON, WHERE, etc) starting 2 spaces
after end of SELECT. This allows me to use std tab to indent to
this second column.
SELECT CONVERT(CHAR(10), oh.OrderDate, 120) AS OrderDate, oh.OrderID, ohs.StatusName, SUM(ol.SalesAmt) AS SalesAmtTotal FROM dbo.Table1 oh JOIN dbo.Table2 ol ON ol.OrderID = oh.OrderID LEFT JOIN dbo.Table3 ohs ON ohs.StatusCode = oh.OrderStatusCode WHERE oh.CustomerID = @CustomerID GROUP BY CONVERT(CHAR(10), oh.OrderDate, 120), oh.OrderID ORDER BY CONVERT(CHAR(10), oh.OrderDate, 120), oh.OrderID
Since many of my colleagues use small/different tab settings, I use
Textpad’s “convert existing tabs to spaces when saving files” feature,
so resulting code is correctly formatted regardless of tab settings.
I too use Textpad for most of my work, purchased it over a decade
ago. Someday I’ll probably switch to an Intellisense-based tool
for coding SQL, but so far I haven’t found anything yet that makes me
faster. I probably spend 10-20hrs/year checking for new tools
like this.
Recently met Eric Wisdahl in an Ybor City pub after Tampa SQL
Saturday. Great guy. Smart guy. I personally don’t
stretch out code as much as his example above, but at least I could readily maintain his code w/out
having to first reformat it for understanding.
March 7th, 2010 at 5:47 pm
Grrr… pre code gets stripped, not sure how Phil was able to do this in #7 above.