SQLServerCentral / Discuss Content Posted by Darwin Hatheway / Article Discussions / Article Discussions by Author / Two Best Practices! / Latest Posts

RE: Two Best Practices!

YSLGuru — Fri, 17 Dec 2004 11:23:00 GMT

I too have to deal with poorly structured queries from the others in my company. After having to deal with this for several years I have come to realize that the problem lies within the way many, mostly developers, typically view the SQL language. I believe most see SQL as something you sit down with a book and learn in 30 minutes. That's it. So long as you can write queries to do what you want then the query is a success. They seem to have no appreciation of the art of the query. I find this interesting as a developer is often very peculiar about there own VB or C code yet they do not apply that to working with SQL.

Unfortunately every one of the posts that contains an SQL statement has not retained its original structure and so everything runs together and therefore the reader can not appreciate what the poster was trying to convey. I personally believe that the proper structure of a query is:

Capitalize table names, table aliases and reserved word such as SELECT, FROM & WHERE.

Each clause (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, etc) should start on a new line

Fields within the SELECT clause should use camel notation where the first character in each word is capitalized and all other characters are lower case

All fields should be aliased.

All joins should be done within the FROM clause unless there is some result set that can not be produced using that logic

Indentation should be used but not over used. Using 8 spaces for indentation is overkill and just makes it very difficult to read the query when it contains more then a few levels of indentation. After all who out there really needs to see 8 spaces to understand that you are use indentation?

Lastly, queries should be written neatly and with good form. I personally believe that developers write sloppy queries because they do not respect SQL and do not feel it deserves any of their time. Because of this they as do others who rush query construction take short cuts to save time. Shortcuts are the seeds of failure.

Robert, I am speculating here but I bet that you find it easier to use a,b & c for table aliases because you have been in the industry for a long time. It was not that long ago that memory was very precious and so every space in your code was expensive. This lead developers to write code in as brief a manner as possible so as to conserve on memory. Because of this it would have made sense to use single character table aliases in the convention of A,B,C, etc so as to minimize the space needed to reference the table. I bet that since you were probably first exposed to SQL in this type of environment that it is what you are most comfortable with and that is why you favor it over what the articles author has described as the best practice for table aliases. It has been my experience from dealing with persons of varying ages in the software industry that people tend to feel most comfortable with what they learned first. So developers who learned VB several years to '.Net' are hesitant to embrace VB.Net because it is a big step from VB6 or if they do embrace it they find it hard to switch to the VB.Net mentality. This is of course purely speculation.

RE: Two Best Practices!

John Scarborough — Fri, 17 Dec 2004 08:20:00 GMT

Really enjoyed the article. I think however, we all know the best way to name table aliases and variables: After ex girlfriends and cartoon characters. ;-)

RE: Two Best Practices!

Michael Lysons — Fri, 17 Dec 2004 01:48:00 GMT

Ironic that in this issue of SQLServerCentral we have an article about formatting (with very easy to read SQL examples) and this article with code that was unreadable at a glance, so I gave up on it.

RE: Two Best Practices!

Robert-161128 — Tue, 27 Jul 2004 19:45:00 GMT

As a soon to be senior citizen, I would like to put in my 2 cents worth on the suggested naming convention for aliases. Being an over 40 developer with less than perfect eye sight, I find it much easier to distinguish between alias like a, b, or c than it is Mnemonic aliases like clo and clc. The similarities between aliases like clo and clc are some times distracting and easy to confuse. Plus it adds addition characters to the statements, which adds to the statement's length. I suppose the idea of readable code would depend on the eyes of the beholder.

RE: Two Best Practices!

rhandloff — Tue, 23 Dec 2003 07:12:00 GMT

It is also worth pointing out that in SQL Server 2000, in addition to making your code more readble, you also get a noticable speed improvement by using ANSI joins.

RE: Two Best Practices!

David.Poole — Thu, 18 Dec 2003 01:24:00 GMT

I tend to use short meaningful abbreviations, which is fine for databases with a few tables, but if you get an app with thousands of tables then your abbreviations cna only be local to the view, function or proc.I remember looking at an SAP application, where there were tens of thousands of tables. The names of the tables themselves were pretty bloody meaningless in the first place.========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

dlhatheway@mmm.com — Wed, 17 Dec 2003 13:38:00 GMT

When I wrote the article, I originally included some very brief remarks on formatting but the article was starting to get rather long, so I focussed on the two Best Practices that would make my life easier and I cut the references to formatting. If something shows up in front of me that looks ugly merely because the spacing and tabbing is poor, that's easy to fix if I feel it's necessary in the situation. Fixing up table aliases and separating join conditions from "this query" where restrictions is MUCH more time-consuming.I believe an earlier reply had it right: develop a style but, as for imposing it on others, it's like arguing religion. Our local SQL Style Guide simply tells the individual programmers to develop some sort of style and stick with it. When I'm looking at something someone else wrote, I can usually get along OK as long as it has SOME sort of style.My experience has been that most programmers seem to develop some sort of spacing/tabbing style of their own without being told to and that it's usually readable.I also like fixed fonts for coding.

RE: Two Best Practices!

Steve Jones - SSC Editor — Wed, 17 Dec 2003 11:22:00 GMT

I have to admit that I tend to use single character, sometimes 2 char aliases, but they have some bearing to the tables. For example, I might useselect * from products p inner join orderline oi on p.productid = oi.productid inner join orders o on oi.orderid = o.orderid inner join customers c on o.custtid = c.custidAs mentioned above, the same tables appear often, so you get used to p for products.ANSI syntax, while strange at first, is 1000 times better than the old syntax.I always use non proportional or fixed width fonts as well and spaces instead of tabs so that the code looks the same on my machine as yours. And vice versa, which is usually the more important one :DSteve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjonesThe Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/www.dkranch.net

RE: Two Best Practices!

skawczynski — Wed, 17 Dec 2003 10:34:00 GMT

I agree with the second best practice but feel that the code example is still somewhat hard to read. I like to format select statements as follows:select distinct clo.clone_id, clc.collection_name, clo.source_clone_id, clo.image_clone_id, lib.library_name, lib.vector_name, lib.host_name, loc.plate, loc.plate_row, loc.plate_column, clo.catalog_number, clo.acclist, clo.vendor_id, clc.value, lib.species, seq.clusterfrom clone clo inner join collection clc on clo.collection_id = clc.collection_id inner join library lib on clo.library_id = lib.source_lib_id inner join location loc on clo.clone_id = loc.clone_id inner join sequence seq on clo.clone_id = seq.clone_idwhere clc.short_collection_type='cDNA' and clc.is_public = 1 and clo.active = 1 and clo.no_sale = 0 and seq.cluster in (select cluster from master_xref_new where type='CLONE' and id='LD10094')

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 08:35:00 GMT

quote:
Dude should have normalized his database...

Probably bought the list from a spammer!========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

Philip Kelley — Wed, 17 Dec 2003 08:32:00 GMT

I'd like to add to all the above threads.I tend to use two characters for table aliases; this generally works, because some tables pop up in almost every query, and you get quite familiar with them. For subqueries and other complex abstractions, I tend to use short words or phrases to make it clear what the subquery's doing.The specifics are vague and abstract, but there are certain forms or conditions where a (left?) outer join cannot be properly performed using pre-ANSI-92 [ANSI 89, right?] syntax. Exclusively using ANSI-92 avoids ever even having to think about this. (This issue is documented somewhere in BOL.)For spacing, I always use non-proportional fonts (for any coding job) and spaces for indentation, and that you can cut and paste most anywhere with impunity. I've developed a complex and rigorous code layout convention... but, when it comes right down to it, everyone has their own systems and standards, and getting anyone to agree on anything is about as productive as arguing religeon. Devise something that works for you, get others to use it if you can, adapt those parts of their conventions that make sense, and be prepared to be flexible. (I will say that I'd never add blank lines within a single T_SQL statement, no matter how long. I use blank lines to tell when one (long) statement has ended and the next one has begun.)Rather than "tit", would "git" be at all appropriate? (From and American who watched loads of Monty Python at a young and impressionable age...)And you missed "rouding out" the Hitchhiker's Guide / Database analogy. The immortal who'd set about insulting everyone (third book?) showed up early on and insulted Arthur Dent... and then showed up at the end of the book and did it again, only to realize "Wait a minute, didn't I already do you?" implying that his list was corrupt and he'd probably have to start all over again. Dude should have normalized his database... Philip

RE: Two Best Practices!

lansley — Wed, 17 Dec 2003 08:17:00 GMT

I second the recommendation on using ANSI joins. It's much clearer. However, it's important to remember that for outer joins the results may not be the same between ANSI and non-ANSI syntax. For ANSI syntax, the join logic is applied before the filter logic. For non-ANSI joins, the filter logic is applied before the join logic.Many articles have been published describing this.Because of this, I am always suspicious of the creators intent and/or skill when viewing non-ANSI joins.Larry AnsleyAtlanta, GA

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 07:50:00 GMT

quote:
For the second best practice, i would like to add Give proper alignments or Indent while writing the joins to make the query more readable.

Nice in theory, but it relies on everyone's Query Analyser being set to use the same font and font size.I've got both Enterprise Manager and Query Analyser using the same font and size but they still show indents at different positions.I've found that leaving blank lines between key blocks of SQL aids readability For exampleFROM dbo.Customers AS cust INNER JOIN dbo.Orders AS Ord ON cust.Id = Ord.CustomerId INNER JOIN dbo.OrderDetails as Det ON Ord.Id = Det.OrderId========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

jwiner — Wed, 17 Dec 2003 07:48:00 GMT

I liked the article...I am amazed that people still use pre ansi-92 syntax...it has been over 10 yrs already.In addition to using ansi join syntax, I like to move the where clause restrictions to the join level where applicable...that way all of my join information is in the same spot.

RE: Two Best Practices!

linto — Wed, 17 Dec 2003 07:33:00 GMT

For the second best practice, i would like to add Give proper alignments or Indent while writing the joins to make the query more readable.Linto

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 07:16:00 GMT

Yep, Douglas Adams. The robot was Marvin the paranoid android but I'm thinking of a character in a later book in the series but can't think of his name.There is a message for all computer programmers in this book when the computer, "Deep Thought" finally gives his answer to life the universe and everything but says that the programmers should have specified the ultimate question first! If that doesn't sum up user requirements then I don't know what does.I particularly liked God's last message to his creation "We apologise for the inconvenience".In addition to your warm beer and black-pudding I think you should add haggis and bagpipes (I know we didn't invent bagpipes but we do persist in playing them).========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

Frank Kalis — Wed, 17 Dec 2003 05:49:00 GMT

You mean the Douglas Adams' book?If so, wasn't this character the robot?Me thinks the British have served the art of insulting very well in having black puddding and serving warm beer Frankhttp://www.insidesql.dehttp://www.familienzirkus.de

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 04:25:00 GMT

Sorry, forgot the language barrier.They say that eskimos have thousands of names for snow, the British have millions that can be used as insults.Bodily parts can be used as insults and tit is one of the milder ones. Given the nature of the bodily part its can even be regarded as an affectionate insult.British insults range from "affectionate greeting" to genuine full-blooded "I'd like to do unto you what God did unto the Sodomites"!People's names can also be used in a derogatory sense. For example the phrase "doing a Mandy" refers to a scam in the property trade that was carried out by one of our politicians (Peter Mandleson) where you sell a £300,000 house for £250,000 (thereby falling into a lower tax threshold) but charge £50,000 for fixtures and fittings.In fact the British have turned insulting people into such an art form that the mere sending of a Christmas card can be deemed to be a gross insult (according to one of our more useless politicians).If you have ever read "The Hitchhikers Guide to the Galaxy" there is a character in it who had immortality thrust upon him. Unable to cope with the shock of finding himself imortal he decided to devote his time to insulting each an every being in the universe personally. I'd like to think of him as British.========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

Frank Kalis — Wed, 17 Dec 2003 04:01:00 GMT

TIT ????TIT Taste in Television (Married with Children) TIT Teacher in Training TIT Technician Induced Trouble TIT Tenchi In Tokyo (anime) TIT Tennessee Institute of Technology TIT Ternary Digit TIT The Internet Times TIT This Is Terrible TIT This Is Thailand (Bangkok Post) TIT This Is True TIT Time in Transit TIT Titus TIT Tokyo Institute of Technology TIT Torpedo Impulse Tank TIT Trotters Independent Trading TIT Trotter's International Trading TIT Tugaya Institute of Technology TIT Turbine Inlet Temperature The results from http://www.acronymfinder.com/af-query.asp?String=exact&Acronym=titFrankhttp://www.insidesql.dehttp://www.familienzirkus.de

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 03:29:00 GMT

I used to work on an HP3000 mini-computer where the console had ultimate control followed by administrators followed by users.We had a utility that let us impersonate differing levels of access.To impersonate the console we typed GOD.To return to administrive roles we typed MORTAL.To impersonate a user we typed TIT.Enough said========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

Frank Kalis — Wed, 17 Dec 2003 03:24:00 GMT

Ah yes, ask the user what he wants and he starts thinking (maybe the first time) on what he's doing the whole day In another forum I found an interesting definition of USER

quote:
"USER, n.: The word computer professionals use when they mean 'idiot.'" Dave Barry

When you deal with government, do you get your payment in time?Frankhttp://www.insidesql.dehttp://www.familienzirkus.de

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 03:05:00 GMT

In my environment yes because

I have a suitable carrot (the keys to the bosses drinks cabinet and the ability to sign expense forms).
I have a suitable stick (I contribute to their appraisals).
I practice what I preach.
Our projects come back to haunt us and when they haunt the person performing the exorcism may not necessarily be the person who originally wrote the code.

I stress that the projects that come back to haunt do so, not because of poor quality, but because we deal with government projects and tend to get repeat business requiring reworking due to fundamental changes in requirements.========================He was not wholly unware of the potential lack of insignificance.

RE: Two Best Practices!

Frank Kalis — Wed, 17 Dec 2003 02:37:00 GMT

quote:
As a DBA I expect all submitted code to be commented.The comments should detail up front which tables are going to be used, what variables if any are declared and what they are supposed to be etc.========================He was not wholly unware of the potential lack of insignificance.

Does it work?Frankhttp://www.insidesql.dehttp://www.familienzirkus.de

RE: Two Best Practices!

David.Poole — Wed, 17 Dec 2003 01:56:00 GMT

As a DBA I expect all submitted code to be commented.The comments should detail up front which tables are going to be used, what variables if any are declared and what they are supposed to be etc.========================He was not wholly unware of the potential lack of insignificance.

Two Best Practices!

dhathewa@isd.net — Wed, 03 Dec 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dhatheway/twobestpractices.asp