March 2, 2008 at 9:48 am
I BELIEVE THAT ALL UPPER CASE ANYTHING IS MORE DIFICULT TO READ AS IT PLACES THE EMPHASIS ON THOSE PARTS OF THE CODE THAT ARE ALSO COLORIZED (pink, blue and gray). ON THE OHTER HAND USING LOWER CASE KEYWORDS (WHICH WILL STILL BE COLORIZED) AND THEN USING MIXED CASE (CAMEL OR PASCAL) FOR COLUMNS MAKES THE CODE EASIER TO READ. I HAVE TO WORK WITH ORACLE AND BELIEVE ME THIS SQL SERVER IS A HUGE IMPROVEMENT IN CODE READABILITY. THROWING IN UNDERSCORES MAY HELP ON THE READABILITY BUT IF FIND THEM CLUMSY TO TYPE.
MAYBE I WAS READING IT INCORRECTLY BUT LAYING OUT CODE IS A "STYLE" ISSUE. HOW OBJECTS ARE NAMED WITHIN A DATABASE CONTRIBUTE THAT STYLE. SIMPLE DATABASES WILL GENERALLY MATURE INTO MORE COMPLEX DATABASES AND IT'S GOOD THING IF YOU DON'T HAVE TO REWRITE A DATABASE WHEN IT TAKES ON THOSE NEW FUNCTIONS.
--Paul Hunter
March 2, 2008 at 10:00 am
I think the naming and style are related, but not necessarily the same, especially when looking at tools. Naming should be standardized within your organization so code can be easily shared. I'm not necessarily a fan of some of the ideas give, but I'd go along with them within a company to be sure things were consistent.
I think style (spacing, casing, color, newlines ) should also be standardized. It definitely helps. I've upper-cased keywords and not upper-cased them. These days with color coding, I'm not sure that it helps that much to upper case them. If you write much SQL code, I think your eyes naturally pick out keywords easily and as the last post showed, all Upper case is definitely hard to read.
I do think layout options can help.
Ex 1.
select a.name, b.address, b.city. b.state, a.phone, c.country
from customers a inner join address b on a.cid = b.cid inner join countries c
on b.countryID = c.countryid where a.customer = 'Jones'
Ex 2.
select
a.name
, b.address
, b.city
, b.state
, a.phone
, c.country
from customers a inner join
address b on a.cid = b.cid inner join
countries c on b.countryID = c.countryid
where a.customer = 'Jones'
Ex 3.
select
a.name
, b.address
, b.city
, b.state
, a.phone
, c.country
from customers a
inner join address b
on a.cid = b.cid
inner join countries c
on b.countryID = c.countryid
where a.customer = 'Jones'
Which is better? I don't like A because it's harder to read for me, harder to pick out joins and conditions. 2 and 3 are both better, but I prefer and try to standardize places on 3 so I can see the joins applied to the tables and the conditions easily.
There are times that I see lots of code posted and it's hard to read. Often I need to reformat it in notepad or SSMS to make sense of it before I can answer a question.
March 2, 2008 at 10:52 am
Right on the money Steve for style #3 and the leading comma's are great. The only thing left for me would be tabs vs spaces. Personally, I prefer tabs (set at 4 characters) as alignment is easier to maintain using fewer key strokes. You could convert tabs to spaces but that makes subsequent edits more difficult.
One thing I haven't seen covered is column aliasing. There are two styles for that and one is syntactically more correct but I believe the other is preferable. Most of the reasons alias a column are; 1) maintain unique column names in output or 2) provide a name for a calculated value. The ANSI way of doing things is to use column|expression [as] SomeAlias. The one I like is SomeAlias = column|expression[.u]. Many of those expressions can get quite long and I think the second style is easier than haveing to find the end of the expression to find the name. Also, you can block out your code to make it more readable.
I guess the style question, as asked by Steve, can best be explained as readable code is understandable code is maintainable code.
An addition to the above maxim is that you should explain why you're doing things with well thought out comments. I like headers that explain why the code was written, what is the codes purpose. Don't tell me you're looping when you create a cursor or iterate thru a table (temp or otherwise). The code shows how you're doing it but leaves the why out.
--Paul Hunter
March 2, 2008 at 11:19 am
Steve,
I took your first example and ran though my favorite online formatter and it came out as this:
select a.name
,b.address
,b.city
,b.state
,a.phone
,c.country
from customers a
inner join address b
on a.cid = b.cid
inner join countries c
on b.countryID = c.countryid
where a.customer = 'Jones'
which I definitely prefer. Note I have to change the default options each time I use it for case and commas, but it works for me.
I've not told you which formatter deliberately, as I don't want to be accused of viral advertising 🙂
Dave J
March 2, 2008 at 7:55 pm
I use the dkranch-manure-spreading-formatting-finger machine. 😉
I am supposed to learn SQL Refactor and some other tools, but to date I've never used a code formatting tool. Years ago I tried Embarkadero's RapidSQL for it's offline editing (in the SQL 6.5 days), but didn't like it. I switched from old style to ANSI joins, picked up this style from somewhere, and have been using it ever since.
I prefer spaces, since they preserve formatting. I have always looked for the "replace tab with spaces" option in editors and we've always mandated that developers use a consistent number of spaces.
March 3, 2008 at 1:00 am
I'm anal about formatting and embedded documentation. I imposed rigid formatting requirements on all the developers in the form of publicised SQL Standards. Everyone knows what I expect from the git. As a result, all SQL code is uniform, easy to read, and self expanatory. Futher, I review every peice of code that goes into the system and any code that doesn't meet the standards is rejected until it does. There are no execeptions.
Sounds harsh, I know. But when I first got to the company I currently work for, there were no rules or standards. Change controls (which were more an OxyMoron), took 3 hours with multiple failures and panic fixes. There were 640 deadlocks per day and 4 "outages" per day. Most nightly routines took 8 or more hours to run. It would take someone 2 days to research code to make a 2 line fix. Now, change controls generally take 15 minutes with no failures, there are only 12 deadlocks per week, there are no outages in any day, most nightly routines have been reduced to 30 minutes or less, and it generally takes less than 15 minutes to research code to make a change, no matter how complicated.
There is no substitute for quality code and quality code includes formatting for readability and understanding. Take pride in your code, always.
Just in case you;re wondering how anal I am about formatting of code and certain other aspects of "standardized" SQL, Ive attached the standards that I enforce at my current company. There will be those that say they can't or wouldn't operate under such guidelines... that's good... I wouldn't want them as a member of my team. 😉
I replaced the company name in the attachement with XYZ and I've removed the company logo... other than that, no other changes have been made in the attachment.
Wanna have some fun? I've see a lot of rhethoric about how people format code. My chalenge to all of you is to take sp_SpaceUsed... give it your best shot... reformat it and comment the code as if it were your own. Steve and Tony, you collect the code. On April first, post all of the submissions and let the "collective" be the judge. There should categories... readability, best style, best comments, I dunno... you make them up.
Wadaya think? Sound good?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2008 at 2:11 am
Am I missing something?
Why can't the layout be based on the user's own preferences?
Store the SQL in an XML doc, then just render it as per the user's requirements.
--
Visual Studio does a lot of the formatting in .net for you. Most people learn to live with it. Why can't SQL be the same?
March 3, 2008 at 2:40 am
My gripe is always with inherited code. I believe I have a good, consistent way of laying out my SQL but often I'm working on legacy code which has been worked on by 3 or 4 other developers in the past. The options are:
1. Keep to the original styling used by the author.
2. Continue with the most recent person's style.
3. Start using my own style, adding to the mix of stles in the page.
4. Take far too much time re-writing it in my own style.
Personally I'm not so bothered about having a standard - I believe that people who write badly formatted code will do so regardless and any change now will not help matters for many years to come due to the amount of legacy code already in place.
JMHO.
Pete.
March 3, 2008 at 4:45 am
My personal gripe is the way the View Designer in SSMS crams as many fields as possible on one line (with arbitrary breaks) and puts the JOINs at the ends of lines rather than the beginning. And, AFAIK, there's now way to change this! (This is on top of the fact that as of SS2K5 it doesn't understand 'CROSS APPLY').
My preferred format is to have field selections one per line with the comma as a leading item (so can easily be commented out) and then have keys on a line by themselves, with indenting of parameters.
So this...SELECT dbo.tblUser.UserID, dbo.tblUser.FullName, dbo.tblProcess.Process, dbo.tblProcess.ProcessID
FROM dbo.tblUser INNER JOIN
dbo.tblDepartment ON dbo.tblUser.DeptID = dbo.tblDepartment.DeptID INNER JOIN
dbo.tblDepartmentProcess ON dbo.tblDepartment.DeptID = dbo.tblDepartmentProcess.DeptID INNER JOIN
dbo.tblProcess ON dbo.tblDepartmentProcess.ProcessID = dbo.tblProcess.ProcessID
...becomes...
select
u.UserID
, u.FullName
, p.Process
, p.processid
from
dbo.tblUser u
inner join
dbo.tblDepartment d
on u.DeptID = d.DeptID
inner join
dbo.tblDepartmentProcess dp
on d.DeptID = dp.DeptID
inner join
dbo.tblProcess p
on dp.ProcessID = p.ProcessID
Of course, if the view created is all that's needed, I probably wouldn't go back and change the layout just for the sake of it (it's obvious that SSMS created it - but it would be nice if it added a comment to that effect - so I'd probably just use View Designer for simple changes).
Also, when writing a Quick-And-Dirty selection, I'll often just cut-and-paste bits together so get whatever happens to work. 🙂
Derek
March 3, 2008 at 6:54 am
Steve Jones - Editor (3/2/2008)
...Ex 3.
select
a.name
, b.address
, b.city
, b.state
, a.phone
, c.country
from customers a
inner join address b
on a.cid = b.cid
inner join countries c
on b.countryID = c.countryid
where a.customer = 'Jones'
As a SQL developer who may have to write 100's of queries in a typical project, I use a style that most closely resembles Steve's example 3, but with a bit more indenting in places, as other have shown on here.
I like it because:
(1) easy to read and follow...the parts jump out at you (to me at least)
(2) easier to debug (because it is easier to read and follow)
(3) easier to comment out pieces for testing/debugging without having to break lines up with comments
(4) easier add additional parts like a select column, join table, or where clause
One comment on naming (sorry, I couldn't resist), leave out the underscores in object names, as other folks here have suggested. Put a capital letter (CamelCase or similar) where you would use an underscore. After you write a few hundred stored procedures for a project where every object name contains 2 or 3 or more underscores, you will understand why I don't care much for them...those underscores really slow me down.
A good style and layout isn't necessarily 'personal preference' as much as it is commonsense and about readability. You probably fully understand your code, YOU WROTE IT, but others may not as easily. Remember, you probably are not the last person that will have to wade through your voluminous code...think about the people after you who have to maintain and update it, and act accordingly.
If it was easy, everybody would be doing it!;)
March 3, 2008 at 7:47 am
One thing I find useful is to first see if the vendor of the tool has any suggested standards. It appears that Microsoft offers little guidance in that regard, for SQL Server. So, what next? Why take a look at Microsoft's code...like the system stored procedures. Oops, no help there, they are all over the place too. If the vendor can't maintain consistency within the code they themselves write, what hope does anyone else have?
Personally, I don't care how the code is formatted or what naming convention you use. What I care about are consistency and clarity. Do the same thing consistently, and make your code understandable...that is, do your best to make your *intent* blindingly clear. Code like you are coding for your grandmother...assuming your grandmother is not a programmer, that is. don't assume you will always be around to fix your bugs...be kind to the person who has to come after you and clean up your mess, make their job as easy as possible (and understand that it just might be *you* that has to comeback to that code in a year [or longer] and fix it). Follow whatever standards are in place at your place of work, and if there aren't any, make some and stick to them (and preferably, document them).
March 3, 2008 at 8:34 am
paulhunter (3/2/2008)
One thing I haven't seen covered is column aliasing. There are two styles for that and one is syntactically more correct but I believe the other is preferable. Most of the reasons alias a column are; 1) maintain unique column names in output or 2) provide a name for a calculated value. The ANSI way of doing things is to use column|expression [as] SomeAlias. The one I like is SomeAlias = column|expression[.u]. Many of those expressions can get quite long and I think the second style is easier than haveing to find the end of the expression to find the name. Also, you can block out your code to make it more readable.
This is unfortunately no longer just a question of coding style. the "Alias by assignment" syntax has been deprecated - so the choice has been made for us....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 3, 2008 at 9:13 am
I still prefer KEYWORDS in all caps. Sure the IDE colorizes them for you but that doesn't help when you print the code on your old B/W laser printer. Or email it. Or read it in a post on SQL Server Central.
I like the "one column per line, comma first" layout for long lists of columns for the same reasons people have already mentioned. But I freely admit it is really, really hard to over come the habit of putting the comma where my grade school English teachers (all nuns with big wooden rulers) said to put it! 😉
March 3, 2008 at 9:38 am
I can see that I am in the presence of greatness! As such, I don't have anything to add that hasn't already been stated except that my bookshelf is experiencing a dearth of relevant publications for my most recent adventure in a new development effort.
I especially appreciate fuller.artful('s) comments and reasoning on object.verb vs verb.object. Insightful and concise logic in an emotional world, which, I find, is at the heart of the problem. Standards should be truth based on fact. Management is often relational, and thus, emotional. To approach standards from an emotional perspective is to cloud fact with feeling and thereby obfuscate the very purpose of the standard with concern for how the standard impacts the individual. I for one am all for standards. I think everyone should adopt my standards because it would make me feel better about myself. Furthermore, I ...
Sorry, I'm still recovering.
----------------------------------------------------------------------------
"No question is so difficult to answer as that to which the answer is obvious." - George Bernard Shaw
March 3, 2008 at 9:49 am
CAGreensfelder (3/3/2008)
I still prefer KEYWORDS in all caps. Sure the IDE colorizes them for you but that doesn't help when you print the code on your old B/W laser printer. Or email it. Or read it in a post on SQL Server Central.I like the "one column per line, comma first" layout for long lists of columns for the same reasons people have already mentioned. But I freely admit it is really, really hard to over come the habit of putting the comma where my grade school English teachers (all nuns with big wooden rulers) said to put it! 😉
Me too! On all counts. Upper case for keywords, one column per line, and the old school ideas regarding commas. I never saw commas first until SMS, and that is one of the issues I have with SMS v. SQA. If there really is an advantage to comma first, it is extremely miniscule.
I'm also happy to hear that I am not the only person with stunted little fingers. I do however use underscores whenever it improves readibility.
When editing another programmer's code, I prefer keeping to the standard that person started with, assuming they had a standard. The exception I usually make to this is when I come across code with everything in lower case, and it looks more like a paragraph from a book than code that has structure.
Jeff, I don't have time to read your document today, but I doubt I would have issues conforming to your standards. I never said I couldn't adapt, just that I didn't like the idea of enforced standards, if the standards weren't useful.
Viewing 15 posts - 16 through 30 (of 90 total)
You must be logged in to reply to this topic. Login to reply