Coding Standards Part 2 - Formatting
-
Steve, Thanks for the great article.
One thing you did not mention (in Part 1) is the common practice of using only singular names for table names. This has helped me many times--or should I say that not following this practice has caused many headaches. My advice is that when naming a table pretend it has only one record in it.
I like your idea of leading commas and also putting the join statements on the beginning of a line. Most format tools put the join command on the end of the previous line which causes much confusion for me. I usually end up rearranging them so I can read the statement. When will the visual tools begin allow us to dictate the format they use.
Dave Catherman
-
Bravo, Steve! A superb description of formatting standards, as well as the justification for using each element!
I have used almost exactly the same standards since shortly after I started working with SQL Server 6.0, many eons ago. My only difference is that I set my tab stops to 4 spaces instead of 3 - this gives me the option to indent something just half a tab stop if I feel it needs it. And I do just that with BEGIN/END pairs - it makes matching up BEGIN/ENDs much easier when they are nested inside the control-flow statement with which they are used, and the inner code is then indented to the next full tab stop.
One other thing I like to do is to code SQL reserved words in ALL CAPS. I know this is a little redundant when working in Query Analyzer, thanks to the color-coding, but it makes queries and scripts that much easier to read when you export them to another tool, say Notepad, or Outlook to e-mail them to a colleague.
Kindest Regards,
Matt Gauch
-
Excellent article. I've had my share of headaches trying to decrypt poorly formatted code. I've also noticed that many of the people I've helped, located the problem themselves while watching over my shoulder as I formatted it.
I have to disagree about the practice of putting the commas before the column name in the select list, though. I find it terribly distracting, while not adding significantly to the debug process, for this simple reason: If you put the commas first, you can comment out any line you like except the first one. It doesn't have a comma, so line #2 will break. If you put the commas afterwards, you can comment out any line you like except the last one, for the same reason. So I always put them last to make it more readable.
- I enjoyed the article and feel very strongly about the need for proper standards so as to improve readability. Nothing starts the day off worse then a sloppy query It seems that many have lost the appreciation for the art of the query. There are a few things I personally do differently that I'd like to mention just for a different perspective.I believe in proper use of spacing just as you however I believe that some often take it to an excess. I feel that indentation is easily visible using 2 to 4 spaces. Why does someone need 8 spaces in order to see that an item is indented? This excessive indentation makes it difficult to work with the query when that indentation goes beyond a few levels of nesting because text spills off the right side of the screen.I do not believe that placing each item on a separate line as you have done within the SELECT clause is the best over all approach for the same reason a few others have mentioned and that is keeping as much of the query in a single screen as possible. Now I do not let the need to see the whole query cause me to do something like place the SELECT & FROM reserved words on the same line and yes there are some queries that are just too big to fit on a single screen no matter what you do. When this happens I simply fit what I can on a single screen while maintaining good form and structure. For me the ability to view the entire query at the same time is a major benefit that not only allows me to read the query very quickly but I am able to quickly make changes and review in my mind what changes to the query would have on its results.Again this is just a different take on style. I do not believe there is any one style that is right. What is important is that each use some structured approach that works well for them and results in neat, effective and efficient queries. What is most important is that each do what they can to write neat well structured code. This shows that the user cares about what they create and are not just throwing together whatever will work so they can move on.I'd like to thank Adam Cogan for posting the links to the naming conventions at the ssw site. These are great!
Kindest Regards,
Just say No to Facebook! - On the GUI tools.....I too enjoy working with GUI tools for SQL. Ent Mgr 2000 has a wonderful tool that is one of the most under used products I have ever come across and that is the Query Builder (QB). To my knowledge there is no way to directly access QB. You must navigate to the Tables node in Ent Mgr and then right-click on a table and select Open Table->Query. This launches QB and it contains the table that you right-clicked on and nothing else. You can use QB to quickly construct a query. You can build any type of query such as SELECT, INSERT FROM, INSERT INTO, UPDATE, DELETE and Create Table. I have shown QB to many in my organization that are not yet comfortable with working with SQL via Query Analyzer. I love QB because it allows you to quickly and I mean very quickly build just about any query you want. In fact I would wager that there is no way that any person no matter how good they are can build a query in a text editor, even Query Analyzer, faster then one can using QB.Another neat feature in QB is that if you already have a query then you can remove the table listed in QB when you launch it and paste your query in the third section of the QB screen which is where the actual SQL syntax of the query built in QB is located and when you exit that section QB will reconstruct in the GUI that same query. This neat trick in QB will allow you to quickly clean up a users query by pasting it in QB and using the GUI to fix up the query in terms of naming conventions and syntax. It does not assist with formatting options such as indentations. The other limitation in QB is that it can't handle CASE statements. Aside from those things QB is a very robust and very useful tool.Now as much as I love QB I have to admit that it is a crutch as are all GUI tools for working with queries. If you are using a GUI tool to build queries because you can't using Query Analyzer or some other text editor then you have not properly educated yourself in the SQL language. There is nothing wrong with using the GUI based tools so long as you can build the same query without having to use the GUI tool. The GUI should be used to help speed up the process, not replace it. If you are having to use a GUI to build queries then do yourself a favor and get a good book on SQL and learn the language. You are only cheating yourself when you let the GUI do the work for you because you can't do it yourself.
Kindest Regards,
Just say No to Facebook! -
Agree with your standards. Never thought of indenting the FROM, WHERE etc by only two spaces. That would make "folding" in a text editor easier to use.
I would also suggest that all INSERTS include the field names, i.e., INSERT INTO ##t_temp1 (col1, col2, col3) SELECT a,b,c from xxx. I find it easier to understand what the prior person is trying to do if the code has.
-
Wow, SQLServerCentral.com now does mind-reading!
I was ready to add my 2 cents to this thread, when I noticed that my post to a similar thread from August 2002 was already there!!
Best regards,
SteveR
-
A most excellent article. Two more cents more than two years late, but who's counting?
Glad to see that I'm not the only person using "leading comma" format. One reason to do this not yet mentioned: this style makes it very hard to forget a comman. (Ever burn five minutes debugging a query only to find you overlooked a comma in the midst of your case-statement jungle?)
Cent number 2: tab key yes, tab character no. I set tabs to 4, but have them insert spaces. This way your text always lines up in non-proporitonaly typefaces, regardless of interface (Query Analyzer, Notepad, Word, Excel, email-of-choice, etc.) An example: Steve's article says he sets tabs to three characters, but in the article's code samples the tabs throw stuff a bunch of picas [picas? in software code? Why?] across the screen, blowing away his format.
Philip
-
Liked/agreed with pretty much everything but the prefix v on the views. I use the suffix "_v" and it gets the point acrosss without getting in the way.
Same with stored procs -- I use [group_]object_action[_qualifier]. Where:
- [group_] -- is for grouping tables and is optional(most often by department or function) like sls_ for sales, hr_ for human resources, xref_ for lookup tables
- Object -- is the base object name -- say Customer or Company
- Action -- is the action the procedure performs (get, ins, upd, del, sav, etc.)
- [_qualifier] -- is any additional info that may help describe what the proc does
As a sample this yields
- sls_Customer_del
- sls_Customer_get
- sls_Customer_get_ByState
- sls_Customer_get_BySalesRep
- sls_Customer_sav (this is combo insert/update)
--Paul Hunter
-
Yo are correct -- it's a crutch. There are many things Query Builder can't handle -- like tha case statement or return multiple results or receive xml for the import parameter or test. The list gets longer and make QB a poor choise for editing many of the sql objects that exists in most databases.
--Paul Hunter
-
About formatting: please do write neat statements. In my job as a DBA I do a lot of DB & app optimizing and it's nice to have good formatted statements. If you don't have good formatted statements, check this:
I found this http://www.sqlinform.com/ on the internet. I use it all the time whenever I am performance tuning that badly written Database application we use at our company.
This one application just writes down a select statement on 1 (!!!) line and it consists of selects, joins, subqueries, cases, casting, convert, sum, having, groub by, more subqueries and more.... I just grab it in profiler, paste the code on sqlinform and voila, I can read the SQL statement. Unfortunately the code still contains all keywords known to mankind... 🙁
JP
-
Thats a nice littel utilty there. I personally would love to see something that can add to this the ability to add table & field aliases using a best pratices approach meaning that all TABLE names and aliases are captalized and the field aliases are done in the form of
AS 'Alias Name'
Obvously the field alias would need some pattern to use and so I'd be fine with the field alias being the form of
'Table Alias_Field Name'
Even though that may not be as descriptive a field alias as woudl be est used it would still be better then a query that has no field alias at all.
I am curious about one thing when it comes to well formed SQL code. I don't understand why so many believe that placing each field within the SELECT clause on a new line looks best. To me it seems like such a serious waste of space just like those who use 8 spaces for a tab (how many spaces does one need to see something is tabbed?) I personally dislike the new line for each field approach because in lengthy queries it requires constant scrolling up and down of teh screen just to read the thing. I find that when fields are aliased as they should be and done in the form of
AS 'Field Alias'
WHere the keyword AS is used and the field alias is surounded in single quotes that it is very easy to spot each field individually in a single line. This allows me to add as many field items to a singel line as will fit within one width of my screens resoution whcih I keep at a modest 1280 x 1024 so that chances are that anyone who reads my quries will also see the entire line in a single screen width. I personally find that when properly aliasing field names like this and placing more then one field on a single line I am often able to see the entire query in a single screen and that is a serious plus. Being able to see the query in it's entirety at once makes troubleshooting and eidting a heck of a lot easier.
So my query is why is the new field on a new line so popular with people?
and..
Why do so few use the AS with single quotes when using field aliases? Even though the langauge doesn't require that fields be aliased in this manner it still makes the query easier to read and besides there are several things that are done in queries that aren't required but are done for ease of readability.
Thanks.
Kindest Regards,
Just say No to Facebook! -
Placing a column on a single line is easier when you're developing new queries. You can easily place a remark BEFORE the field or AFTER the field (esp. if you use the sortcutkeys CTRL+SHIFT+C / R):
SELECT
ID
, Firstname
-- ,Lastname
, Street -- just the name of the street
, City
FROM
tblPersons
WHERE
lastname = 'smith'
-- AND FirstName = 'Tom'
JP
-
You hit the nail on the head and is what I've been evangelizing about for years. I also detest the aliasing format of SomeColumn As SomeOtherName and prefer the style of SomeOtherName = SomeColumn. Additionally and block and line up the equal sign. I'd only alias a column when it's absolutely necessary.
There again I fall back on my old standard of using the standard that's present even if it's really bad. After all I just write the code -- I don't own it.
--Paul Hunter
-
Well if you try working with a query using some interface that doesn't support syntax highlighting and you compare this
SELECT T.Field As 'Alilas'
FROM TABLE T
verses this
SELECT T.Field Alias
FROM TABLE T
You'll see very quickly that the first instance that uses complete syntax is far easier to read and therefore use. If you look at these two in something that does show syntax highlighting like Query Analyzer you'll see that the first is still easier to read because the use of the keyword AS and the single quotes around the alias make it quick and easy to locate field and their alias. You might want to change your take on field aliases because their useage is starting to become required in more and more areas. For example Crystal Reports as of version 9 requires all fields to be properly aliased. It really is better to alias fields regardless of the syntax or methdo you use for field aliases. As SQL Server progresses the product is demanding users more fully qualify their statements. For example in SQL Server 2005 you can get away with out fully qualifying your query meaning you can leave out schema however your query performs faster when you fully qualify it.
Besides what's the downside to fully qualifying a query aside from the fact that it adds a few extra secs to the query construction? I'm not making any accusations about why you or anyone else has a preference for query constrcution that excludes fully qualifying the query but what I can state for fact is that at my company most of our development pepole had this no field alias unless necessary attitude and when our product moved form supporting Crystal 8 to Crystal 9 every single report they had written and to be redone because they did not use field aliases. Leaving out the field aliases saved them maybe a few minutes at best when first creating the reports however they paid a heavy price for that when it came time to edit the reports to work with the newer version of Crystal. And they couldn't balme Crystal for the change because even though they could leave out field aliases in Crystal 8, the products best practices docs recomended always using field aliases.
Whats the point here? Fully qualifying queries are far less likely to require changes down the road but taking shortcuts and trimming your quries down to the bare minimum can come back to haunt you.
Kindest Regards,
Just say No to Facebook!
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply