SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Coding Standards Part 2 - Formatting


Coding Standards Part 2 - Formatting

Author
Message
AdamCogan
AdamCogan
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 6
>Tabs don't appear the same way in text and GUI enviromnent so what looks great and all lined up in query analyzer is an undreadable mess in Access.

Yes I know a number of people who use Access ADPs to do stored proc development - the problem is it screws up any formatted SQL.
My suggestion is to move to Visual Studio.NET to do stored proc development.

>You don't need aliases when you create a query in a GUI environment, because it fully qualifies the names for you automatically, and I personally find it harder to read aliases than fully qualified names.

I agree

>The Answer? I beleive the answer lies in an automatic formatter - does anyone know of one? QUEST software has a fantastic tool for Oracle called Formatter Plus. I've asked them if they're doing one for SQL but they said not at this stage. Maybe if everyone can send them an email, they might get something happening.

I agree it would be nice to have a utility format all the stored procs in a database - we couldn't find a 3rd party util either, so we are currently adding to one of our SQL utilities (unless Brian Lockwood is adding it).

Email me (adamcogan@ssw.com.au) if you want to be told when we release it.

Adam
www.ssw.com.au



Klaas-Jan
Klaas-Jan
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 1
Hi, good article, i agree that proper formatting makes code more readable. I think comment is also very important, will that be described in a next article?

Klaas-Jan



Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2835 Visits: 113
I use a combination of making all keywords upper case along with some indenting and having separate lines for the SELECT clause, the FROM clause, the WHERE clause, etc.

When I first started learning about databases and SQL, I avoided using a GUI to help me learn SQL faster. I continue to write my own SQL (instead of using a GUI) to help me maintain what I have learned but also to avoid having code that doesn't conform to my formatting style.

Robert Marda

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
vince.iacoboni@db.com
vince.iacoboni@db.com
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 553
I agree with most of the formatting comments, but my own style is slightly different. I like the following:


SELECT a.Column1,
a.Column2,
CASE WHEN a.Column1 > 20
THEN 'High'
ELSE 'Low'
END Rating,
SUM(b.Column3)
FROM Table1 a
JOIN Table2 b
ON a.Key1 = b.Key1
AND a.Key2 = b.Key2
WHERE a.Col2 > 10
AND b.Col3 LIKE 'Hi there%'
GROUP BY a.Column1,
a.Column2
ORDER BY a.Column1,
a.Column2


The advantages to me are:
1. Easy to see start of the statement since all one block with no newlines between.
2. Capitalized keywords make finding columns easier
3. If GROUP BY and ORDER BY not included, all columns line up under first
SELECT column.

I personally find the preceding comma distracting (though I understand its utility for ease of editing).

I also searched for a SQL formatter and couldn't find much. I did find a BASIC source for a simplistic SQL formatter, which I extended to support many more keywords. It will take a one-line SQL complex statement similar to what comes out of a GUI tool and format it as above. Email me if interested.

vince.iacoboni@db.com



vince.iacoboni@db.com
vince.iacoboni@db.com
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 553
Oops, formatting killed my style above. Oh well.



Steve Rosenbach
Steve Rosenbach
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 206
Steve - a very good article - thanks very much! I also was struck by practicality of your use of commas ahead of each column in the SELECT.

I'd be interested in anyone's thoughts about formatting the SQL Server CASE expression

Best regards,
SteveR

Stephen Rosenbach



Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142088 Visits: 19421
I tend to format CASE using indents (tabs) for the different lines.

SELECT
case
when x = 1
then 10
when x = 2
then 20
else 50
end 'my col'

As far as the GUI. Personally I do not think it works well for complex SQL, but that's me. I understand why people use it, but with outer joins and unions and cases, it falls down (to me). When I truly need to debug, it is often from Profiler or embedded SQL in an app and the the GUI doesn't really help me. Too slow. Don't really have a recommendation here because I do not use it.

I don't upper case the keywords because I'm lazy. Grew up on Unix/DOS and prefer lower case for most things. However, you should do what makes sense for you and be consistent (standard) for others in your environment.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jodiem
jodiem
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 1
Hey, Vince's VB SQL Formatter Actually works, I like it. It could probably be enhanced a bit with a UI or something but it's great to find that someone has actually done most of the hard work to get to at least a consistent SQL format.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24026 Visits: 1917
I really enjoyed this article as it is filled with great practical advice. It's going to be a link I'm going to forward to all our development groups.

I tend to do most of my SQL coding via a text editor. For me it is faster when compared to the time it takes when I include all the mouseclicks and screens to create relationships, indexes, and the like. Also, where I work we tend to reuse our code heavily. Since I do a lot of proofs of concept which get built upon by others, this is especially true of my stuff.

As a result, GUI generated-code works great... the first time. However, if we're taking scripts we'll need to use again and again, but with slight modifications, formatting is essential. It's not just for luddites. Consider taking a script that has to be used to create twenty databases across four servers all with minor tweaks based on the requirements of individual customers which differ slightly but not drastically. GUIs are too time consuming to build each database. Well-formatted code in scripts is priceless.

Also consider the more common case of building scripts to move from development, to QA, to production. If there is a DBA review, the scripts should be well-formatted. This allows a DBA to be able to follow the code a bit better, speeding up the approval process.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@‌kbriankelley
Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142088 Visits: 19421
Completely agree with Brian (and thanks).

The GUI does cause slowdowns and issues in deployment. A few references (from my point of view):

http://www.sqlservercentral.com/columnists/sjones/wp_gui.asp

http://www.sqlservercentral.com/columnists/sjones/vcspart3.asp

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search