Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enhancing the readability of your code: Table aliasing in sql


Enhancing the readability of your code: Table aliasing in sql

Author
Message
tlehner
tlehner
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 166
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.
rsmith-796599
rsmith-796599
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
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.
nathan.syverson
nathan.syverson
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 20412
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


DavidL
DavidL
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 756
Hooooot!
Leading commas all the way!
CAPS for all sql keywords!
Keep the aliases short and sweet!
:-P

I'd never have guessed the discussion would be this long, but it's a good read on a slow morning!



Kit G
Kit G
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 4113
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45076 Visits: 39909
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:-Dw00t


BWAA-HAA!!! I was greasing up the ol' pork chop cannon for someone else, but I like big easy targets better. :-P I'll be back...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45076 Visits: 39909
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:-Dw00t

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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tlehner
tlehner
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 166
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!
mike.wade
mike.wade
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 40
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
.
:
)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36113 Visits: 18742
Well
, I think leading commands are
, well
, easier to read

Hehe

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search