Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sylvia Moestl... » Enhancing the readability of your code: Table...
Add to briefcase
80 posts, Page 1 of 8
12345»»»

Enhancing the readability of your code: Table aliasing in sql Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Sylvia Moestl Vasilik
Posted Wednesday, May 06, 2009 10:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 16, 2013 4:03 PM
Points: 14, Visits: 336
Comments posted to this topic are about the item Enhancing the readability of your code: Table aliasing in sql
Post #711754
Jeff Moden
Posted Wednesday, May 06, 2009 10:40 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 32,909, Visits: 26,798
But in this particular SQL statement, there's no reason to alias your tables names - it would just save a few keystrokes, at the cost of making it much less readable. Here's what I would do.


I apologize but I must strongly disagree with that statement. While it may appear to make it more readable for you at the time you're writing it, the next poor slob that has to troubleshoot the code will likely not be as intimately familiar with the tables as you are. (S)he will end up having to lookup which tables contain which columns and that takes a comparably long time even if you only have two tables joined. To wit, the practice of not using table aliases on joined queries is probably worse than the practices of either selecting nonrelated aliases or using full table names on every column reference.

And, heh... don't get me started on the practice of using leading commas in code.

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #711757
Leendert van Staalduinen
Posted Thursday, May 07, 2009 12:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 09, 2012 4:42 AM
Points: 29, Visits: 396
From my own practice: I always use table aliases when two or more tables will be JOINed (and never use an alias if no JOIN is involved). Advantages:
- I always understood that the SQL engine will be faster, if field names will be fully qualified (when working within a single database: having schema plus tablename prefices): the engine does not have to find out which schema/table contains the specified field.
- Readability: I certainly prefer to read tens of "TT."-s above tens of "dbo.tblTrabeculectomyTechniques."-s. When I see my SQL code after a few years, I feel comfortable (and I assume my collegues will feel comfortable as well).

Another suggestion: I always use a syntax like: "dbo.tblTrabeculectomyTechniques As TT", instead of "dbo.tblTrabeculectomyTechniques TT". I find this much easier to read.

Leendert.
Post #711779
Mike DiRenzo
Posted Thursday, May 07, 2009 1:00 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143, Visits: 183
Sorry, but I agree with Jeff Moden's reply. The leading comma thing is a bit irritating but to each his/her own. With one or more tables it is not a bad idea to alias but Jeff's point is spot on with regard to the "next poor slob". I can't agree with you more Jeff.

-Mike
Post #711801
Gene Marini
Posted Thursday, May 07, 2009 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 08, 2011 2:19 PM
Points: 3, Visits: 13
What irritates me about this is the use on lowercase for SQL keywords....... Looks amateurish
Post #711802
ALZDBA
Posted Thursday, May 07, 2009 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049
I advise to use aliasses in all queries that involve more than one table, view, ...

This is really not a comparable issue for using leading commas, upper / lower case on key words,... just because those will not generate "ambigous column names" in a case INsensitive db.

Using aliasses will help anyone analysing the query and figuring out from which objects the intended columns come from.

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #711807
Samuel Vella
Posted Thursday, May 07, 2009 2:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 328, Visits: 1,851
Gene Marini (5/7/2009)
What irritates me about this is the use on lowercase for SQL keywords....... Looks amateurish


Each to their own again... I prefer proper case as personally I find it more readable. Keywords are highlighted differently anyway (working on the assumption that their aren't many cases where a developer would be working without syntax highlighting).
Interestingly UK (and many other countries) road signs are always in proper case e.g. London instead of LONDON as proper case is quicker to read

Code readability, much like object naming conventions usually comes down to systems. As long as there is a system in place, e.g. always trailing commas, always upper case for keywords and always using the same aliases for the same main group of tables (every system has a core of at most 10 tables which are used in 90% of the queries) then it doesn't matter what the system is as long as it makes sense and is stuck to within a team
Post #711823
David Hutcheson
Posted Thursday, May 07, 2009 2:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 22, 2012 3:07 AM
Points: 48, Visits: 100
What Jeff Moden said.

I totally agree with his point about leading commas. It might save you an error or two when you forget to delete it from the end of the previous row, but you have completely destroyed the readability of it.
Post #711831
David Burrows
Posted Thursday, May 07, 2009 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 6,351, Visits: 5,365
And, heh... don't get me started on the practice of using leading commas in code.

Oh! Come on Jeff don't be coy now

For the record, when I code SQL I,

Use trailing commas
Aliases when more than one table/subqeury
Aliases always in subqueries
Try to make alias meaningful (ie o for Order, ol or OrderLine etc)
Uppercase SQL keywords
Indent JOIN beneath FROM
Indent ON/AND beneath JOIN (each comparison on separate line)
Indent subqueries and subquery SQL

Far away is close at hand in the images of elsewhere.

Anon.
Post #711834
lfallis
Posted Thursday, May 07, 2009 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 11, 2011 8:19 AM
Points: 35, Visits: 178
Each to their own again... I prefer proper case as personally I find it more readable. Keywords are highlighted differently anyway (working on the assumption that their aren't many cases where a developer would be working without syntax highlighting).
Interestingly UK (and many other countries) road signs are always in proper case e.g. London instead of LONDON as proper case is quicker to read

Code readability, much like object naming conventions usually comes down to systems. As long as there is a system in place, e.g. always trailing commas, always upper case for keywords and always using the same aliases for the same main group of tables (every system has a core of at most 10 tables which are used in 90% of the queries) then it doesn't matter what the system is as long as it makes sense and is stuck to within a team


I agree also (although being from England that may be why).

For anyone who has read a book on GUI's, most would advise to stay away from CAPITALS as they block out the screen more and make it more difficult to read. I always prefer lowercase, using capitals where words are joined and aliases even where there are no joins i.e. from myTable mt join anotherTable at on...

You never know when you are going to have to add a join in, and then you will need to go through and add them for the possible duplicate columns.
Post #711876
« Prev Topic | Next Topic »

Add to briefcase
80 posts, Page 1 of 8
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.