Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Coding Standards Part 2 - Formatting Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2002 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 8:45 AM
Points: 8, 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






Post #36608
Posted Tuesday, July 2, 2002 5:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 10, 2002 12:00 AM
Points: 135, 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




Post #36609
Posted Tuesday, July 2, 2002 6:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #36610
Posted Tuesday, July 2, 2002 6:31 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:38 AM
Points: 716, Visits: 464
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





Post #36611
Posted Tuesday, July 2, 2002 6:32 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:38 AM
Points: 716, Visits: 464
Oops, formatting killed my style above. Oh well.




Post #36612
Posted Tuesday, July 2, 2002 8:48 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:00 PM
Points: 91, Visits: 195
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




Post #36613
Posted Tuesday, July 2, 2002 10:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:51 PM
Points: 33,266, Visits: 15,431
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
Post #36614
Posted Wednesday, July 3, 2002 6:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 19, 2003 12:00 AM
Points: 14, 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.




Post #36615
Posted Wednesday, July 3, 2002 7:24 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 8:57 AM
Points: 6,634, Visits: 1,872
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #36616
Posted Monday, July 8, 2002 10:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:51 PM
Points: 33,266, Visits: 15,431
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
Post #36617
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse