Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

Enhancing the readability of your code: Table aliasing in sql Expand / Collapse
Author
Message
Posted Thursday, May 07, 2009 9:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 6:52 AM
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.
Post #712113
Posted Thursday, May 07, 2009 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 01, 2010 3:15 PM
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.
Post #712135
Posted Thursday, May 07, 2009 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 27, 2013 11:40 AM
Points: 30, Visits: 20,341
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

Post #712149
Posted Thursday, May 07, 2009 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:16 AM
Points: 130, Visits: 530
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!



Post #712151
Posted Thursday, May 07, 2009 9:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 05, 2013 2:13 PM
Points: 829, Visits: 4,099
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.

So, sorry all you leading comma guys, I'm with Jeff on this one.


-- Kit
Post #712158
Posted Thursday, May 07, 2009 10:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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!


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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #712184
Posted Thursday, May 07, 2009 10:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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!

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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #712189
Posted Thursday, May 07, 2009 10:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 6:52 AM
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!
Post #712200
Posted Thursday, May 07, 2009 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 11:01 AM
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
.
:
)
Post #712202
Posted Thursday, May 07, 2009 10:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
Well
, I think leading commands are
, well
, easier to read








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #712204
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse