May 6, 2009 at 10:30 pm
Comments posted to this topic are about the item Enhancing the readability of your code: Table aliasing in sql
May 6, 2009 at 10:40 pm
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
Change is inevitable... Change for the better is not.
May 7, 2009 at 12:10 am
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.
May 7, 2009 at 1:00 am
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
May 7, 2009 at 1:06 am
What irritates me about this is the use on lowercase for SQL keywords....... Looks amateurish
May 7, 2009 at 1:22 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
May 7, 2009 at 2:02 am
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
May 7, 2009 at 2:23 am
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.
May 7, 2009 at 2:35 am
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 :w00t:
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.
May 7, 2009 at 4:40 am
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.
May 7, 2009 at 4:46 am
David Hutcheson (5/7/2009)
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.
Well, I don't agree that it destroys readability, in fact I find it a lot more readable. But then that's me. Darn, I see Jeff rigging up the pork chop bazooka in front of my place. Keep 'em coming boy, I need something for supper! 😛
Be that as it may, I also use aliases as soon as there are 2 or more tables/subselects involved and keep them kinda meaningful. Always upper-case by the way, since I make table/view names upper-case as well. Oh, by the way, I hate the imposing behaviour that SQL2K8 (Un-)Intellisense cases my table names the way they are in the database. Does anyone else have the same gripe?
May 7, 2009 at 4:53 am
Jan Van der Eecken (5/7/2009)
Always upper-case by the way, since I make table/view names upper-case as well. Oh, by the way, I hate the imposing behaviour that SQL2K8 (Un-)Intellisense cases my table names the way they are in the database. Does anyone else have the same gripe?
There is so much wrong with this 🙂
Table object is named in one manner e.g. MyTable
You refer to it as MYTABLE in your queries... removing any consitancies
SQL Server 2008 Intellisense will be using the same case as the table name in the database as a case sensitive server will not equate [MYTABLE] to [MyTable] which will mean your query will fail
I really can't see any benefit in not using the same case as the object naturally has, unless the object has been really badly named (CUSTOMERRELATIONSHIPMANAGEMENTTABLEHEIRACHY) and its a case insensitive server
May 7, 2009 at 4:59 am
lfallis (5/7/2009)
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.
Are you showing REAL table and column names in your GUI's? If you do, don't outsource DB dev to a non-english speaking developer 🙂 Believe me, I have to cope with a database that goes with a product that was created in France. All table and column names are French. And although I do speak French as one of the four languages I mastered (kind of), I have a hard time. Guess what your users would be doing to their hairdo if the word "ordinateur" showed up on their screens. :w00t:
May 7, 2009 at 5:04 am
Samuel Vella (5/7/2009)
Jan Van der Eecken (5/7/2009)
Always upper-case by the way, since I make table/view names upper-case as well. Oh, by the way, I hate the imposing behaviour that SQL2K8 (Un-)Intellisense cases my table names the way they are in the database. Does anyone else have the same gripe?There is so much wrong with this 🙂
Table object is named in one manner e.g. MyTable
You refer to it as MYTABLE in your queries... removing any consitancies
SQL Server 2008 Intellisense will be using the same case as the table name in the database as a case sensitive server will not equate [MYTABLE] to [MyTable] which will mean your query will fail
I really can't see any benefit in not using the same case as the object naturally has, unless the object has been really badly named (CUSTOMERRELATIONSHIPMANAGEMENTTABLEHEIRACHY) and its a case insensitive server
Sorry to disagree, but I don't see what's wrong with this, unless your collation sequence is case-sensitive, which in most cases is a mistake in the first place. What I do have a gripe with is that I want my code to look consistent, which Intellisense prevents me from doing if somewhere in history some developer made one single table name CamelCase, but all the other ones are as per coding standards (whatever those may be).
May 7, 2009 at 5:07 am
Are you showing REAL table and column names in your GUI's? If you do, don't outsource DB dev to a non-english speaking developer 🙂 Believe me, I have to cope with a database that goes with a product that was created in France. All table and column names are French. And although I do speak French as one of the four languages I mastered (kind of), I have a hard time. Guess what your users would be doing to their hairdo if the word "ordinateur" showed up on their screens. :w00t:
Ha, no I wasn't speaking from a database point of view. I meant advice on warnings, messages, general HCI that sort of thing.
😀
Viewing 15 posts - 1 through 15 (of 79 total)
You must be logged in to reply to this topic. Login to reply