Two Best Practices!
-
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.
-
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.cluster
from 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_id
where 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')
-
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 use
select *
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.custid
As 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 😀
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
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.
-
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.
-
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.
-
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.
-
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.
-
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. 😉
John Scarborough
MCDBA, MCSA - 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 lineFields within the SELECT clause should use camel notation where the first character in each word is capitalized and all other characters are lower caseAll 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 logicIndentation 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.Ed
Kindest Regards,
Just say No to Facebook!
Probably bought the list from a spammer!Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply