June 30, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/codingstandardspart2formatting.asp
June 30, 2002 at 6:09 pm
Great article, we follow the naming as per below. These cover some of the same but also more areas of SQL Server naming conventions.
http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_1_ObjectNaming.aspx
http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_4_IndexesNaming.aspx
Adam
June 30, 2002 at 9:00 pm
Good article. In particular the point about putting spaces in so that you can jump from word to word easily is one often overlooked.
Andy
July 1, 2002 at 1:45 am
Yes, good ideas here.
Anything that makes old code easy to read gets my vote.
Has anyone used sp_addextendedproperty to help comment objects within your databases?
July 1, 2002 at 5:36 am
Steve, Agreed with everything you said. I'm particularly fond of commas preceeding column names.
Adam, Thanks for your guidelines.
July 1, 2002 at 8:35 am
Steve,
I totally agree with your comments and I personally use a style very similar to yours, and for the same reasons. We do not actually have a standard yet, but we're just in the process of looking at it now, so your article is timed perfectly.
July 1, 2002 at 8:54 am
Thanks for all the comments. This is something that I have found helpful as well and hopefully others will adopt something similar to standardize their environments.
Steve Jones
July 1, 2002 at 11:46 am
I like the trick of putting the commas before the field name. Looked weird at first glance, but made perfect sense when explained. I will pass this article on to my co-developers; we already used Part 1 of the article to develop our naming standards.
David Poole asked about using sp_addextendedproperty to comment database objects. That is our standard for database documentation. I wrote an ASP app that allows the developer to select a database and view the extended properties for tables, columns, views, etc. Eventually, we want to extend the app to include an extended property for each system that uses a particular object. Our developers are more likely to document if it doesn't involve a word processor!
July 1, 2002 at 5:35 pm
Steve, thanks for the great article, it will create a lot of discussion. Here's my 2c worth.
All of these rules are GREAT if you use a text editor environment. But not so great if you use GUI. I personally always use GUI where possible. I am not able to understand a 50 line long SQL statement with joins on 10 tables without being able to see it graphically. Where these formatting ideas fall down in a GUI enviroment are:
As soon as you view the code in A GUI enviromnent all your formatting disappears.
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.
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.
Basically the point is, unless your whole team now and in the future is going to use exclusively text editors, then you need to be flexible with a coding standard like this, because to re-format a large query created in a GUI enviroment just becuase the people who love to type can't read it, is a waste of time.
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.
July 2, 2002 at 12:31 am
If you have to be a ludite and use a text editor to develop SQL, rather than as suggested by another responder - use GUI it's easier - then it would also be advisable to make a distinction between table and column names and SQL RESERVED WORDS. The best way I have seen so far is :-
SELECT Column_Name_1
,Column_Name_2
,MAX(Column_Name_3)
FROM tbl_Name
WHERE Column_Name_1 > 0
AND Column_Name_2 > 0 .....etc.....
GROUP BY Column_Name_1
,Column_Name_2
Not sure how this will appear in the forum but it looks fine in a fixed spaced editor.
By using UPPERCASE for the reserved words it makes their importance stand out. and much easier to read.
July 2, 2002 at 12:50 am
>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
July 2, 2002 at 5:04 am
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
July 2, 2002 at 6:26 am
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
July 2, 2002 at 6:31 am
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.
July 2, 2002 at 6:32 am
Oops, formatting killed my style above. Oh well.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply