May 7, 2009 at 8:23 am
Feeling hungry, pork chops sound good this am 😉
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 7, 2009 at 8:26 am
Steve Jones - Editor (5/7/2009)
For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.
My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.
+1. This is almost exactly how I code SQL.
What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...
Tim
May 7, 2009 at 8:30 am
tlehner (5/7/2009)
What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...
Leading commas looks grammatically wrong
compare (Addresses used to be written with comma's to seperate each address element - at least thats how I was taught)
Queen of England,
Buckingham Palace,
London,
England
with
Queen of England
,Buckingham Palace
,London
,England
May 7, 2009 at 8:44 am
Gene Marini (5/7/2009)
Do they have computers in France?
Probably they do... we have some in Italy as well.
I find this post very useful for the discussion.
-- Gianluca Sartori
May 7, 2009 at 8:54 am
Sylvia is actually right when she says that there's no need for aliases in author-title query. But Jeff is right when he says that code is not written well for other people. Just as case, comma location, and aliasing should be consistent, so shoud the use of table prefixes. Either use them, or don't; don't just use them for ambiguous columns or for aliased tables.. I'd write the code this way [except I probably wouldn't put in these specific comments]:
select -- I prefer lowercase
authors.Au_id, -- I prefer trailing commas
authors.au_lname,
authors.au_fname,
titles.title,
titles.title_id,
titleauthor.royaltyper
from titles
left join titleauthor -- I use left, right, inner, or full outer before the join to keep my head straight
on titles.title_id = titleauthor.title_id
left join authors
on titleauthor.au_id = authors.au_id
I find it interesting that there has been no mention yet of the most common usage for table aliases that I see: Multiple references to the same table. Here's a simplified example:
select
loanapp.id,
officer.name,
processor.name,
underwriter.name
from loanapp
left join contact officer
on officer.id = loanapp.officer_id
left join contact processor
on processor.id = loanapp.processor_id
left join contact underwriter
on underwriter.id = loanapp.underwriter_id
Does this just happen in my field or with the databases I have to work with?
May 7, 2009 at 9:16 am
I see your point, and use "proper grammar" rules for readability in most of my code, as in:
Left(ColumnName, 1)
instead of
Left(ColumnName,1)
This is just more natural to me.
But at a certain point, that breaks down. Your example isn't SQL, so it's hard to judge SQL based upon it. I look at a select list much differently than parts of an address. I guess I use both leading and trailing commas depending upon situation. Trailing commas when the select list is simple (often w/o newlines for each column), and then at some point I'll switch to newlines and leading commas...especially when I'm not selecting:
select
column1,
column2,
column3
from...
but rather
select
, column1
, case when i.complete = '1901-01-01 01:00:00.000' then 'P'
when i.complete = '1901-01-01 02:00:00.000' then 'M'
when i.complete > '1901-01-01 02:00:00.000' then 'C'
else 'I' end
as status
, right('00' + cast(datepart(minute, isnull(s.datecommitted, z.lastupdate) - s.datecreated) as varchar), 2)
+ ':' + right('00' + cast(datepart(second, isnull(s.datecommitted, z.lastupdate) - s.datecreated) as varchar), 2)
as tot_duration
From...
(BTW, I can't seem to control tabs well in the code blocks on the forum)
The commas show my eye where each column to be returned starts.
May 7, 2009 at 9:34 am
Be careful. I always try to alias tables in my code and mark every column with those aliases, even if the column names are unique to the objects in the statement *at that time*. It has happened to me before that a column with the same name as another column in the statement gets added to one of the other tables in the statement, thus breaking the code with an ambiguous column name error.
May 7, 2009 at 9:46 am
My .02:
I prefer option B
1. It's readable. The aliases don't get into the way of the column, which is what I'm really interested in in the Select.
2. I know where the columns are coming from. I just scan down to the from clause.
3. Aliasing to a fully qualified schema speeds up compiling.
If you're worried about clarity, why not do (something like) this?
Select
a.FirstName, --from Authors a
a.LastName,
a.AuthorID,
t.Title, --from Titles t
t.TitleID
From dbo.Authors a
inner join dbo.Titles t
on a.AuthorID = t.AuthorID
May 7, 2009 at 9:48 am
Hooooot!
Leading commas all the way!
CAPS for all sql keywords!
Keep the aliases short and sweet!
😛
I'd never have guessed the discussion would be this long, but it's a good read on a slow morning!
May 7, 2009 at 9:52 am
I've come across code that used synomyns. Since synomyns are not common for me, at first glance I was like "What? Where's that table coming from? It's not listed in the database! And there's no alias in the code!?!?!" But, I'd figured it out eventually and, looking through the code, when you are constantly using a table from another database, it does help on the readability of the code once you realize a synomym is being used. As I get more used to it, it won't throw me for a loop as often.
The problem I have with table aliases is not having the habit ingrained of where to look for the actual table names. Since I'm looking at the code in sequence, the aliases used in the select statement (the a.col1, a.col2, b.col1, c. col3) sometimes get confusing because the alias is defined later on in the code (in the FROM statement - from dbo.table1 as a) . I think the use of "as" in the defining of the alias is helpful because that makes it easier to spot to then understand where everything in the select part is coming from.
But then, I am also pretty new to the SQL scene, so I'll probably get better at spotting the aliases the more experience I get.
As for the leading commas, it's a preference thing mostly, but since I was forcibly taught in school that the comma has to go directly AFTER the word, that there can be NO SPACES between the word and the comma, I tend to go with the trailing commas. I have no wish to have my grammar school teacher haunting me because I put the comma on the next line. :crazy:
So, sorry all you leading comma guys, I'm with Jeff on this one. :w00t:
-- Kit
May 7, 2009 at 10:14 am
Steve Jones - Editor (5/7/2009)
For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:
BWAA-HAA!!! I was greasing up the ol' pork chop cannon for someone else, but I like big easy targets better. 😛 I'll be back...
--Jeff Moden
May 7, 2009 at 10:16 am
tlehner (5/7/2009)
Steve Jones - Editor (5/7/2009)
For the record, I'm a leading commas guy, Jeff is wrong, and it's easier to read. All you trailing comma folk need to change!:-D:-D:w00t:However, I do think that in your group, you need to be consistent. I'd hate to upper case SQL, or go with trailing commas, but I would for consistency's sake.
My preference is to camel case tables, go with 2-4 letter aliases that are meaningful (as in ALZDBA's example), and stick with lower case for most typing.
+1. This is almost exactly how I code SQL.
What's up w/ the leading comma haters? I suppose you put your ands and ors at the end of a line, too? lol...
Tim
Heh... what's up with all the leading comma lovers? Look at the email you just wrote... no leading commas. 😉
--Jeff Moden
May 7, 2009 at 10:28 am
Jeff Moden (5/7/2009)
Heh... what's up with all the leading comma lovers? Look at the email you just wrote... no leading commas. 😉
Lol...clever! I'll take it as a compliment that I'm able to switch between SQL and English so easily!
May 7, 2009 at 10:31 am
My Opinion
, which is the way i always do it
, is not to confuse the issue but rather
: to simply use grammer as gramer is meant to be ie
:- not like this reply
, which is difficult to read and understand the flow
.
:
)
May 7, 2009 at 10:31 am
Well
, I think leading commands are
, well
, easier to read
:hehe:
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 46 through 60 (of 79 total)
You must be logged in to reply to this topic. Login to reply