SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Coding Standards Part 2 - Formatting / Latest Posts

RE: Coding Standards Part 2 - Formatting

ems035c — Wed, 07 Jan 2009 15:34:11 GMT

I prefer this:SELECT customerid, customername, count(*) AS mycount FROM customers c INNER join orders o ON c.customerid = o.customerid LEFT OUTER JOIN orderlines oi ON o.orderid = oi.orderid AND oi.status = 1 WHERE customerid = 5 AND c.customeraddress is not null AND (c.active = 1 OR c.status > 5) GROUP BY customerid, customername HAVING count(*) > 1 ORDER BY customerid, customernameCap and right alight all the keywords

RE: Coding Standards Part 2 - Formatting

elucid8 — Wed, 02 Jul 2008 17:23:54 GMT

I have been using a very similar standard for years, taught to me by a developer I used to work with. Since then I have been very passionate about keeping to a good standard such as outlined in this article (although I have a few things I do slightly differently). I will also only do these things if it will increase the readability of the block which is the whole point of doing it.I now work for a different company and comparing the 2 standards used is like chalk and cheese (the chalk tastes horrible compared to the old cheese way). The standard at my old work allowed me to open up a 1000 line stored proc that I had never seen before and be able to read very easily what was happening. I could often debug something almost instantly looking at each block of code. Nowadays, I am working for a different company and trying to learn a system where each block of code is written almost like a solid block of text. I especially cannot understand how people can use the column names on 1 line approach and the SQL server standard of sticking the joins on the end of lines.Firstly on the column names, if there is a bug in something, or you need to add a column, especially into an insert statement, or say you forgot a comma, nothing can be more frustrating than trying to count across the screen to find one of 50 columns quite often named inconsistantly from the data that is being put into it (think trying to read a large csv file in notepad and trying to line up which data is in which column - aliases can help though). Then imagine this jammed tight up against a whole bunch of other blocks of code:eg. (this is only a small example but you get the point - it gets worse as it gets bigger)[font="Courier New"]INSERT INTO SomeTable (ColumnA,ColumnB,ColumnC,ColumnD)VALUES (0,'SomeKindOFPieceOFData',0,'SomeOtherReallyLongPieceOfData')[/font]Now try reconciling the columns compared to this:[font="Courier New"]INSERT INTO SomeTable ( ColumnA ,ColumnB ,ColumnC ,ColumnD ) VALUES ( 0 AS ColumnA ,'SomeKindOFPieceOFData' AS ColumnB ,0 AS ColumnC ,'SomeOtherReallyLongPieceOfData' AS ColumnD ) [/font]Aliasing also really helps in this format, so you don't need everything on the same screen at once. I also love the commas at the beginning of the line as (besides looking neater) you can be guaranteed that if you miss one (it happens to the best of us and SQL server is not always forthright in telling you the exact problem when one is missing) it will stick out like a sore thumb and you will instantly see you need to add one in, but if it is on the end of variable length lines you will quite often have to read a whole block to find it.Which brings me to my next point of joins on the ends of lines. A good rule of thumb is to read in your head a statement as though you were reading it out loud, adding pauses for clarity. If someone read you a paragraph of text with no punctuation at all, I bet you would drift off and not comprehend half of what they said. The same is true in SQL.So when someone says: [font="Courier New"]FROM Table1 LEFT OUTER JOINTable2 ON Table1.Column1 = Table2.Column2 INNER JOINTable3 ON Table1.Column1 = Table3.Column3 [/font]...it is interesting to hear them read it aloud and pause at the important spots. Then I have to ask why you would then put the join for Table3 on the line for Table2 when the join parameters have nothing to do with Table2 and they notice that they paused before the join. So get stuff from Table1 -pause- then make a join to table2 in this way -pause- and then make a join to table3 in this way. It just makes sense. The join is related to the table it preceeds. Like so:[font="Courier New"]FROM Table1 T1LEFT OUTER JOIN Table2 T2 ON T1.Column1 = T2.Column2 INNER JOIN Table3 T3 ON T1.Column1 = T3.Column3 [/font]Say it out loud, you know it makes sense. Also, you can then comment out the join, like you can comment out one field in a select list when they are each on their own line, because the join and the table that succeeds it are meant to be together. Yet the GUI tools add the join to the end of the preceeding line and people wonder why they always screw up, i.e. never understand, their joins.I like the use of capitalised keywords too as they make the difference between commands and fields, and also the whole structure, a lot clearer and proper indentation is a must. Whitespace is your friend. The human eye is built to read lists and indentation creates a list like nested structure. Like so (to use an example from the article):[font="Courier New"]--===================================================--Comment about what the block does breaks it up from--the rest of the code so you can see where each starts--and endsSELECT c.CustomerID AS CustomerID ,o.CustomerName AS CustomerName ,COUNT(*) AS RecCountFROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID LEFT OUTER JOIN OrderLines oi ON o.OrderID = oi.OrderID AND oi.Status = 1WHERE c.CustomerID = 5 AND c.CustomerAddress IS NOT NULL AND (c.Active = 1 OR c.Status > 5)GROUP BY c.CustomerID ,c.CustomerNameHAVING COUNT(*) > 1ORDER BY c.CustomerID ,c.CustomerName[/font]So easy to read. And adding the table alias to ALL columns not only helps the proc not to break should a column of the same name be added to one of the other tables, it also allows you to see which table it comes from. When you have large numbers of columns and tables involved and you are trying to find where one of them comes from because it is failing due to a NULL value or something, this can be invaluable (especially when each table may have many columns themselves and you have to go back to each and search through looking for one in particular - very tiresome and a waste of time).I used to use solely GUI tools when I first started and I understand the comfort factor of using them (in fact I find them much quicker to use if I am just running some simple queries to check some data), but unfortunately they are no good when you are dealing with very large stored procs with many, many sql statements in them. You absolutely must be able to read the code, hence the importance of proper formatting. But some people are just lazy and others pay the price.

RE: Coding Standards Part 2 - Formatting

Tom Holden-248075 — Tue, 20 Dec 2005 08:46:00 GMT

Tabs into spaces is nice. You can set that in your editor. I agree with commas preceding each column. If you're not doing that currently I promise you'll like it.

RE: Coding Standards Part 2 - Formatting

maria canahua — Mon, 19 Dec 2005 19:11:00 GMT

HI, thanks for your article,

I've got a question?

We proposed in my work, that the name of columns in table is about semanthic of de the column ie, "what the column is", example Order, ShipName, etc, we recomend NOT USE THE TYPE OF COLUMN, ie int, varchar, string, etc.....

The programmer say: "it's not possible because we need to know the type of de column example strShipName for an rapid development"

I think it is not correct, because what happen if the column change example, from boolean to int, in resume i don't think is better use teh hungarian notation.

Any comments of this?

thanks

zeta

RE: Coding Standards Part 2 - Formatting

BeerBeerBeer — Mon, 19 Dec 2005 17:12:00 GMT

Must have missed this article when it came out last year but I have been using this technique (with capitalised KEYWORDS) for many years now. I try hard to instill it into the new developers and encourage them to ensure that all code, not jsut in the database, contain some menaingful and consistent structure.Considering the complexity of some queries I think it is very important to use the techniques used in newspapers all over the world. Lay things out in columns. That's one of the reasons newspapers can be read so quickly by many. It certainly works for me when coming back to look at a complex query in terms of understanding what the query is trying to achieve.Glad to see I was on the right(?) track

RE: Coding Standards Part 2 - Formatting

Alex-217289 — Mon, 19 Dec 2005 14:51:00 GMT

I agree with everything but using aliases. Though it makes it more difficult to read sometimes because of lengthy table names, debugging and modifications are much easier. For instance, If you have to globally search and replace the name of a table throughout your code, you don't want to have to think of every alias variation that may have been used. The standard should be set so that the full table name is used all the time. That way there is no confusion within the code as to what table it is and no possibility that one of the aliases is missed in a modification to the code.I also agree with making all the reserved words CAPITALized. Makes the code much easier to read.Just my 2 pence

RE: Coding Standards Part 2 - Formatting

Justin Ames — Mon, 19 Dec 2005 14:04:00 GMT

Our coding standards are similar, but mine are a bit more stringent:

/*1.  Having all sections of the same query tabbed after the select clarifies what is and is not part of the query2.  All column names in a multi-table query should be specified as to the owner - you never know when a column of the same name will be added to a joined table, and it slightly increases performance3.  All column and table names should be in camelcase, everything else in lowercase for easier reading and typing4.  Aliased table names should be the first letter of each word of the base table name - in lowercase5.  Aggregate columns should be named for ease of identification6.  The word "as" should be used whenever aliasing.7.  Each sub-part of a section of a query should be tabbed once more than the section header8.  99% of reads can be done safely with the (readuncommitted) locking hint. This can greatly reduce contention.*/select	c.CustomerID	, c.CustomerName	, count(*) as CustomerCount 	from Customers as c with (readuncommitted)		inner join Orders as o with (readuncommitted)			on c.CustomerID = o.CustomerID		left outer join OrderLines as ol with (readuncommitted)			on o.OrderID = ol.OrderID				and ol.Status = 1	where 		c.CustomerID = 5 		and c.CustomerAddress is not null 		and (c.Active = 1 or c.Status > 5)	group by		c.CustomerID		, c.CustomerName 	having		count(*) > 1 	order by		c.CustomerID		, c.CustomerName

RE: Coding Standards Part 2 - Formatting

t berry — Mon, 19 Dec 2005 08:49:00 GMT

The standards outlined by Steve Jones are exactly what I have adopted - they help so much when you're dealing with large scripts and more so - someone elses scripts!

I have two additional standards:

1. All INSERT, UPDATE and DELETE scripts must be enclosed by /* */ so that they are not accidentally executed.

2. All INSERT, UPDATE and DELETE scripts must start with BEGIN TRAN.

Tom Berry

RE: Coding Standards Part 2 - Formatting

YSLGuru — Tue, 31 May 2005 10:01:00 GMT

First off I don't make any decission to make anyone's lives difficult. The use of CR is a management decission which I have to deal with just as anyone in my organization. And just because you think CR is not worth anything doesn't mean that everyone esle is suppose to ditch it in favor of your preferred rpeorting tool. Don't assume that the person posting ssomething always has absolute control over everything in their company. Are you able to make any kind of change you wish at your company?

Second off I am not criticizing your style I am merely inqurying about it. The SQL language often allows one to do the same thing via many different paths. The style used for writing that code is also something that can vary. While your style is what you like it does not mean it is the best or the only one applicable. I too have had positive feedback from my style. The difference between us is that I see others differences as just that, difference and not simply inferior and taking the attitude that mine is the only way.

Lastly, field alilases. I guess that Microsoft is just wrong here and your right. The fact that MS has taken the stance that field aliasing is a good thing by changing SQL Server 2005 to rely on it more so then SQL 2000 is just wrong since it disagrees with your stance or am I reaidng your last response incorrectly? If your reply is nothing more then a criticism instead of a crituque then don't bother replying. I'm always interested in meaningful dialog and not in taking shots back and forth.

RE: Coding Standards Part 2 - Formatting

SQLNightOwl — Sun, 29 May 2005 14:36:00 GMT

Let me see if I got this right. Crystal Reports can't handle changes to your schema so it's time to make developers lives more dificult. Let me suggest you dump CR and get DataDynamics ActiveReports or SQL Reporting Services or Excel or even just using the output directly from Query Analyzer. Maybe that's a little over the edge -- but just a tiny little bit <g>. In my opinion, Crystal is little better than a stone table and chissel.

What I use is Query Analyzer for coding so I get syntax highlighting and quite honestly a blue keword "As" is not a usefull as something like this...

select o.CustomerID = c.ID, Customer = c.Name, OrderID = o.IDfrom Customer cjoin Orders o on c.ID = o.CustomerID

What I like is consistent, neat and blocked-out code. As a consultant, I've had the opportunity to show my guidelines to both developers and managers and have gotten a very positive response from both groups. Mostly because it stresses good conventions that seldon need to be aliased (unlike the sample above.)

Paul

RE: Coding Standards Part 2 - Formatting

YSLGuru — Fri, 27 May 2005 07:06:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

SQLNightOwl — Thu, 26 May 2005 19:14:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

JP de Jong-202059 — Thu, 26 May 2005 06:19:00 GMT

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):

SELECTID, Firstname-- ,Lastname, Street -- just the name of the street, CityFROMtblPersonsWHERElastname = 'smith' -- AND FirstName = 'Tom'

RE: Coding Standards Part 2 - Formatting

YSLGuru — Thu, 26 May 2005 06:10:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

JP de Jong-202059 — Thu, 26 May 2005 01:06:00 GMT

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... :-(

RE: Coding Standards Part 2 - Formatting

SQLNightOwl — Fri, 22 Apr 2005 01:36:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

SQLNightOwl — Fri, 22 Apr 2005 01:20:00 GMT

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)

RE: Coding Standards Part 2 - Formatting

Philip Kelley — Mon, 20 Dec 2004 16:32:00 GMT

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

RE: Coding Standards Part 2 - Formatting

Steve Rosenbach — Mon, 20 Dec 2004 09:50:00 GMT

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

RE: Coding Standards Part 2 - Formatting

Jim Johannsen — Fri, 17 Dec 2004 15:43:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

YSLGuru — Fri, 17 Dec 2004 13:18:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

YSLGuru — Fri, 17 Dec 2004 13:17:00 GMT

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!

RE: Coding Standards Part 2 - Formatting

CDJorg — Fri, 17 Dec 2004 10:56:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

Matt Gauch — Fri, 17 Dec 2004 10:31:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

David Catherman — Fri, 10 Dec 2004 07:55:00 GMT

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

RE: Coding Standards Part 2 - Formatting

michael.rosquist — Thu, 29 Aug 2002 11:18:00 GMT

http://pueblo.lbl.gov/~olken/X3L8/

RE: Coding Standards Part 2 - Formatting

Greg M. Lucas — Thu, 29 Aug 2002 01:26:00 GMT

Excellent Celko article (as always).Does anyone have a link for the Metadata Standards Committee naming conventions he discusses in that articl?Cheers, . . Greg

RE: Coding Standards Part 2 - Formatting

Steve Jones - SSC Editor — Tue, 27 Aug 2002 09:02:00 GMT

I read a bunch of Mr. Celko's posts in Usenet when I was getting started and learned a lot.However, as mentioned above. I like the punch card formatting for #1. For precisely the reason Joe mentions in his article. I can easily remove a line or two with a simple comment and can easily read the statement.I'll admin that in a long column list this is harder to work with since the whole statement no longer fits on the screen, but faster for editing and testing.#5 - Agree. No reason to prefix things. With .NET, MS has gone away from this notation.#6 - Agree. I thought it was interesting when I worked in a database (3rd party) the had a "person" table. with an ID column. Every other table that referred to this was "personid". Same for product. The product table had "id", and other tables had "productid". Made sense. Ran into problems when there were two foreign keys. Like "studentid" and "teacherid" both referring to student.id.Seemed good, got slightly confusing over time. Not that it was a bad design idea, just not implemented consistently. Personally I like writing student.studentid = class.studentid rather than student.id = class.studentid.Steve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjones

RE: Coding Standards Part 2 - Formatting

Steve Rosenbach — Tue, 27 Aug 2002 07:30:00 GMT

>>What are your opinions on numer 1, 5 and 6 on http://www.intelligententerprise.com/001205/celko1_1.shtml?<<Hi Michael!Thanks for the great link to Joe Celko's article, "Ten Things I Hate About You - SQL needs to break old habits and find its own voice" Excellent reading as always from Mr. Celko. Although I concede that Joe is one of the guru-est of all SQL gurus, I must beg to differ with him on item 1 "Punch Card formatting." In my experience, formatting a SQL statement in the way Joe says *not* to, makes it more readable, not less. It is exactly the same argument that he makes for using mixed case - the structure and patterns help make the statement more readable. On Item 5 - "Prefixes on variables and schema objects" - I would generally agree with Joe. There just isn't a good reason to do it. Moreover, putting prefixes on column names to show data type can really mess you up if you ever have to change the data type - you may have to go all through the database changing constraints, relations, etc.I don't use "tbl" anymore as a prefix for table names (coming from MS Access, this was a habit hard to break.) But, I think using something like "vw" as a prefix for a view name still makes sense. My reasoning is that it's nice to instantly know that there is a view involved when you see something like...FROM vwTrendingByMonth vt INNER JOIN IncidentCodes ic ON vt.IncidentCode = ic.IncidentCode...As to Item 6 - "Different Names for the Same Data Element" - I must plead guilty of doing this both ways at times. I think I see Joe's point about an attribute being an attribute no matter which entity it belongs to. Also, his point about prefixes making it hard to find, for example, all of the address columns in the database - in case you need to put contraints on them - is a good one. I have to think about this one some more.I agree with Joe on his items 2, 3, 4 and 7. On Item 8 - "Needlessly proprietary code" - I also agree - and the keyword is "needlessly". I think portability of SQL is sometimes overemphasized -- why not take advantage of ANSI 92 join syntax in SQL Server, for example, if there is virtually no possibility that you will ever move to Oracle, which only supports "WHERE clause" joins? But I agree there is no good reason for *not* using the ANSI 92 syntax in SQL Server in cases where it also supports a non-standard or older syntax. BTW, one of the things I enjoyed most about Joe's article was this statement in Item 8: "The poor Oracle people are forced into proprietary code because their product is so bad, which is another issue." Thanks, Joe! I always wondered why such a high-performance DBMS continues to have such a poor implementation of SQL. Best regards,SteveRStephen RosenbachArnold, MD

RE: Coding Standards Part 2 - Formatting

michael.rosqvist — Tue, 27 Aug 2002 05:49:00 GMT

What are your opinions on numer 1, 5 and 6 on http://www.intelligententerprise.com/001205/celko1_1.shtml?

RE: Coding Standards Part 2 - Formatting

Greg M. Lucas — Thu, 22 Aug 2002 01:46:00 GMT

We've recently started prefixing column names with a two or three letter prefix to identify table. This makes writing joins much easier and helps instantly identify columns and their respective tables in a more complex query. This makes every column name unique across the database and does away with the need for aliases in everything except triggers. So a basic join might look like this:

SELECT        CUS_Customer_ID      , CUS_Customer_Name      , COUNT(ORD_Order_ID)      , SUM(ORD_Order_Value)FROM      CUSTOMERINNER JOIN      ORDERON      CUS_Customer_ID = ORD_Customer_IDGROUP BY        CUS_Customer_ID      , CUS_Customer_Name

This works for us but I would be interested to hear what others think.Greg

RE: Coding Standards Part 2 - Formatting

Steve Jones - SSC Editor — Wed, 21 Aug 2002 15:12:00 GMT

I think you should go with what works. Personally I find the indentations easier to read, but that's me.Steve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjones

RE: Coding Standards Part 2 - Formatting

Ramesh — Tue, 20 Aug 2002 19:36:00 GMT

I would go for something like this as it is very easy to read and you can see the columns being selected,How many tables being referenced andthe join conditions pretty handy for documentation too:-)SELECT *FROM customers, orders, order_detailsWHERE customers.order_id = orders.order_id and orders.order_id = order_detials.order_id and order_details.amount > 100Edited by - ramesh on 08/20/2002 7:39:41 PM

RE: Coding Standards Part 2 - Formatting

Steve Jones - SSC Editor — Mon, 19 Aug 2002 09:48:00 GMT

Great point. I wrote an article on Templates in Query Analyzer and how they use them, but had to release copyright to SQL Server Magazine. The link is here and I highly recommend templates for any development.http://www.sqlmag.com/Articles/Index.cfm?ArticleID=21176Steve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjones

RE: Coding Standards Part 2 - Formatting

mdenner — Fri, 16 Aug 2002 13:21:00 GMT

Good article and thoughtful responses -- one topic not covered: as a consultant, I write a lot of TSQL scripts and have been playing around with different file headers making use of SQL2K Query Analyzer's 'Replace Template Parameters' function. I've appended my basic template (which may look terrible in this post depending on how short your displayed line width is), but I'm curious what other people find useful.

 /*========================================================================== Name:    <Name of script, String, A Script>		Version:  	1.0 Author:  <Author's name, String, Mark A. Denner>, Baker Robbins & Company (email <author's email, String, mdenner@brco.com> Creation Date: 	<Date created, Date, Today's Date> Created for: <Client's name, String, Client Name> All rights reserved.==========================================================================DESCRIPTION-----------<Description of script, String, Description>DEPENDENCIES------------<Dependencies or assumptions, String, Dependencies or assumptions>ARGUMENTS---------<Arguments or options for the script, String, Arguments or options for the script>UPDATE HISTORY--------------1.0	<Date created, Date, Today's Date>	<Author's initials, String, MAD>	Created*/

RE: Coding Standards Part 2 - Formatting

Steve Jones - SSC Editor — Mon, 08 Jul 2002 10:01:00 GMT

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.asphttp://www.sqlservercentral.com/columnists/sjones/vcspart3.aspSteve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjones

RE: Coding Standards Part 2 - Formatting

K. Brian Kelley — Wed, 03 Jul 2002 19:24:00 GMT

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 Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: Coding Standards Part 2 - Formatting

jodiem — Wed, 03 Jul 2002 18:39:00 GMT

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.

RE: Coding Standards Part 2 - Formatting

Steve Jones - SSC Editor — Tue, 02 Jul 2002 10:19:00 GMT

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 Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjones

RE: Coding Standards Part 2 - Formatting

Steve Rosenbach — Tue, 02 Jul 2002 08:48:00 GMT

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 expressionBest regards,SteveRStephen Rosenbach