| Author |
 Topic  |
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
 Posted - 03/28/2007 : 09:53:45
|
quote:
Originally posted by X002548
quote:
Originally posted by blindman
That's a rather personal attack on my qualifications as a SQL developer
Are you serious? You of all people?
Good Lord...are you just trolling?
Brett -he was talking to me, and he had a point, my post wasn't well written that he responded to and came off poorly, and I apologized. As for trolling, don't "troll" by bringing it up since it's already been resolved and he had a point.
- Jeff
http://weblogs.sqlteam.com/JeffS
|
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 03/28/2007 : 09:57:26
|
No, he doesn't have a point, and where you actually attacking is credibility? I don't think so, in any case, as I pointed out earlier, this has always been a topic of debate with the blind dude, of which I think he is incorrect...and again, if we could do a poll, I would believe his view would be in the minority
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 03/28/2007 : 10:15:39
|
quote:
Originally posted by X002548
quote:
Originally posted by jsmith8858
Brett -he was talking to me
And if he was, then why post it in a public forum?
Brett, he replied to and quoted my post. you're responding to me, right? you did it in a public forum and not via email.
Come on, relax my man! 
- Jeff
http://weblogs.sqlteam.com/JeffS
|
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 03/28/2007 : 11:44:56
|
| Jeez....I kinda wished I didn't raise a flippant remark to (re)start an unknown flame war!!! |
|
|
|
Gelder
Starting Member
United Kingdom
7 Posts |
Posted - 03/28/2007 : 12:01:33
|
| No there is only one reason for the alias on the from statement and that's for readability there is no performance gain or lose either way. |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 03/28/2007 : 12:48:48
|
Yes, I honestly feel the your second example has more clarity than the first. In your first example I start at the top of the SELECT statement and immediately have to jump down to the FROM clause to figure out exactly what "Name" refers to. While in your second example the exact derivation of each column is immediately obvious. This problem becomes even more pronounced when the statements get longer than can be displayed on a screen without scrolling, or involve more joins.
quote:
Originally posted by jsmith8858And this is a very simple SQL statement. Imagine something more convoluted!
I don't have to imagine. I deal with much more complex SQL statements every day. And thanks to my avoidance of aliases I don't have to imagine, or guess, or remember, what single-character code represents what table or object. That allows me to concentrate on SQL logic rather than SQL decryption.
quote:
Originally posted by jsmith8858The less your eyes need to process, the easier you can pick out things like join conditions and expressions, the easier it is to understand what you are working with.
The less your eyes have to jump around the code to figure out what is going on, the less likely you are to make an error.
quote:
Originally posted by jsmith8858I will ask again, show me an example where you can demonstrate that a complicated SELECT with short, concise and meaningful aliases is *harder* to read and work with rather than one with long table names repeated over and over.
Your example above is fine, for the reasons that I gave.
quote:
Originally posted by jsmith8858As for my analogies, they all hold. My point isn't about using fully qualified object references or not, it is about how we use SHORT and CONCISE labels to reference things in programming that we need to refer to over and over ALL THE TIME. Whether it is a class library or a long tablename or a fully qualified object in a hierarchy or something else entirely, it is easier for a programmer to work with short, meaningful abbreviations rather than long, verbose descriptions of things, by using local variables or local aliases or whatever the language you are using allows. It's a good programming practice to follow to make things easier and simpler and shorter as long as things are also still efficient and clear.
Of course. Verbosity has no value for its own sake. What I am talking about is information content. An object name should be long enough to sufficiently describe its nature, and no more. Truncating the name to something shorter reduces the information content. That is a simple fact.
quote:
Originally posted by jsmith8858Do you name your tables like "CustomerNameAndNumberAndStatusAndType
Of course not. "Customer" sufficiently describes the content of the table. Let me ask you this: Why don't you name your table simply "C" rather than "Customer"?
quote:
Originally posted by jsmith8858I respect your opinion if you personally feel that aliases are not your personal thing, but to suggest that in general they should be avoided and are only used by people who don't like to type or who are lazy is completely wrong.
I was responding to this general statement:
quote:
Originally posted by AndrewMurphy
but the one who says "aliases improve readability"...wins that other arguement!!!
I happen to disagree with that statement, and I did not feel like letting it go unchallenged in a forum where it would be seen by new SQL developers who might take it as gospel. To date, nobody has given me a good argument for the gratuitous use of aliases.
I completely agree with jezemine's quote of Einstein "Make everything as simple as possible, but not simpler." Unnecessary use of aliases violates this principle.
quote:
Originally posted by jezemineblindman: what do you do when you need to do a self join? I guess you have to use aliases then. Customers1 and Customers2 in that case? and do you really fully qualify classes in java/.net code? you must have a wide monitor!
You have not been reading my posts very carefully.
quote:
Originally posted by X002548
No, he doesn't have a point, and where you actually attacking is credibility? I don't think so, in any case, as I pointed out earlier, this has always been a topic of debate with the blind dude, of which I think he is incorrect...
Go ahead and disagree with me, but yes I do have a point which I have explained several times. I have given a very simple and logical reason for my coding style, which I have employed succesfully on extremly complex SQL statements for more than a decade now. Nobody has given me a similiarly simple and logical reason for the use of aliases except that it reduces the number of ascii characters in a statement. Brevity is desirable, but it is NOT the goal.
jsmith, I appreciate your willingness to discuss this in a rational manner. I'm looking for concrete benefits to throwing aliases on every table in a statement. Maybe this would be better as a new thread?
e4 d5 xd5 Nf6 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 03/28/2007 : 13:38:57
|
Often, when a debate occurs on a web forum or even face to face, you have to stop halfway through and ask yourself: What, exactly, is the other person's point? Often we argue about two different things. I think that is happening here.
In this case, you have been arguing about something which no one is stating. NO ONE thinks that just "throwing aliases on every table in a statement" is a good idea, especially when you are implying by "throwing" that it is just randomly being done "as a rule" without any regard for WHY it is being done. No one thinks that you should alias the table "Cust" as "Cst" or "C" to save space. you are arguing that ALWAYS using aliases and using them POORLY makes a sql statement harder to read. you are correct! However, I (and the others, I believe) am arguing that when you have a long, complicated sql statement with long table names, using intelligent aliases makes the code shorter (a fact) easier for a person to scan for content and structure (a fact) and easier to work with (an opinion). if it is your opinion that it is difficult to quickly associate "c" with "customers" even though it is clearly spelled out for you in the FROM clause, then that's fine, that's your opinion.
What I don't get is this: The first thing I teach anyone about reading or writing a SELECT is this: you START with the FROM clause FIRST and the LAST thing you read or write is the SELECT columns. In this case, the FIRST thing you do when reading or writing is establish the tables involved and their aliases (if any).
Now, if you want to ignore the fact that you can (and should) read the FROM clause first before "processing" the SELECT clause, then tell me what this snippet means:
select customers.customerID,
custTotals.Amount
from
Well, it must mean we have two tables in the database, customers and custTotals, right? Wrong! CustTotals is a derived table! But how would we know that? Well, we *must* read the FROM clause to know this. It is silly to suggest that it requires "extra work" to process the FROM clause and to identify aliases, when they are used correctly and intelligently (notice I keep repeating that theme and you keep implying the opposite in all of your arguments), since it is a REQUIREMENT of reading any SQL statement to process the FROM clause and all JOINS before any other part of the SQL statement in order for it to make any sense! When reading ANY SQL statement, you CANNOT assume that the label before a column name is a table -- that would be ignoring derived tables completely, as well as aliases! It would be wrong to make such an assumption -- by DEFINITION, you MUST determine what all labels refer to in a SQL statement before you can understand ANY of it -- where clause, select clause, join conditions, etc. Creating a rule that says "never use aliases" does NOT mean you do not have to perform this step!
Unless, derived tables are "bad" too ??
EDIT: fixed some typos ...
- Jeff
http://weblogs.sqlteam.com/JeffS
|
Edited by - jsmith8858 on 03/28/2007 13:58:09 |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 03/28/2007 : 16:13:43
|
quote:
Originally posted by jsmith8858
However, I (and the others, I believe) am arguing that when you have a long, complicated sql statement with long table names, using intelligent aliases makes the code shorter (a fact) easier for a person to scan for content and structure (a fact) and easier to work with (an opinion).
But this was YOUR example, which I assumed you meant to illustrate intelligent use of aliases:
select
c.Name,
o.OrderID,
o.OrderDate,
o.OrderedBy,
d.Qty,
d.Rate,
d.Qty * d.Rate as Amt,
d.Flag,
case when d.Flag = 1 and d.Qty = 0 then
1 else 0 end as Status
from
Customers c
inner join
CustomerOrders o on c.CustomerID = o.CustomerID
inner join
CustomerOrderDetails d on o.OrderID = d.OrderID
where
o.OrderDate >= '1/1/2000' and o.OrderDate < '12/31/2000' and
d.Flag = 1
order by
c.CustomerID,
o.OrderDate DESC
This is exactly the type of gratuitous use of aliases I reject. This is a SHORT piece of code, with SIMPLE logic, and SHORT table names. But actually my argument against aliases gets stronger as the complexity of the code increases. Go ahead and start reading sql statements with the FROM clause, but if there are a dozen tables joined together each with a single-character alias then you must still start by memorizing the substitutions before you analyze any of the rest of the code. Without aliases, you can start right in examing the logic. So again, what did the use of aliases get you except for saving a few keystrokes?
quote:
Originally posted by jsmith8858
Now, if you want to ignore the fact that you can (and should) read the FROM clause first before "processing" the SELECT clause, then tell me what this snippet means:
select customers.customerID,
custTotals.Amount
from
Well, it must mean we have two tables in the database, customers and custTotals, right? Wrong! CustTotals is a derived table! But how would we know that?
A better question at that point would be "Why would we care?" "Customers" and "custTotals" tells me all I need to know at that point about what exactly customerID and Amount refer to. Whether they are tables or views or functions is irrelevant at that point in the analysis. What WOULD be inscrutably useless is seeing this:
select c.customerID,
cT.Amount
from
That tells me absolutely nothing.
I'll ask you again, why bother naming a table "Customers" instead of simply "C"? That would satisfy your desire to make your code shorter, wouldn't it?
quote:
Originally posted by jsmith8858
It is silly to suggest that it requires "extra work" to process the FROM clause and to identify aliases, when they are used correctly and intelligently..
It is not silly at all. Memorizing a dozen aliases before diving into code logic requires extra effort, and if you are working on comparing two complex queries from two different developers who each used a different set of aliases, your work is that much harder.
quote:
Originally posted by jsmith8858
When reading ANY SQL statement, you CANNOT assume that the label before a column name is a table...
I'm not assuming it is a table. I'm just hoping it is something descriptive.
quote:
Originally posted by jsmith8858
by DEFINITION, you MUST determine what all labels refer to in a SQL statement before you can understand ANY of it
That is not true at all, as you yourself just illustrated! I knew the purpose of your customers/custTotals code snippet eve though you OMITTED the FROM clause! So I don't know what "DEFINITION" you are talking about.
quote:
Originally posted by jsmith8858
Creating a rule that says "never use aliases" does NOT mean you do not have to perform this step!
Jeff, you started your last post by accusing me of arguing against something that nobody has been saying. So where, exactly, have I said "never use aliases"?
You may be amused to know that I used table aliases in some code today, because the object naming convention at my client is redundant and loaded down with pointless prefixes and object definitions. "abc_rpt_lead_report" and "abc_rpt_lead_report_staging", for example. These names are overly verbose, so I had no issue with using aliases. What I did NOT do was use aliases like "r" and "s". Instead, I gave these tables the aliases "Report" and "Staging". The minimum label required for any coder coming along to understand what the data represents.
e4 d5 xd5 Nf6 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/28/2007 : 17:04:54
|
I doubt that we can convince blindman to use aliases to shorten even the Customers table. And I doubt that blindman can convince Jeff or most other people in this forum not to use aliases. It's a subjective matter.
But +1 for aliases. I think example 1 in Jeff's post from page 2 is much easier to read than example 2. In example 1, I can more quickly find the column names than in example 2 as I don't have to scan for the period. As a result, I prefer short aliases (1-3 characters).
Tara Kizer
http://weblogs.sqlteam.com/tarad/ |
Edited by - tkizer on 03/28/2007 17:07:42 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 03/28/2007 : 21:04:45
|
quote:
Originally posted by tkizer
I doubt that we can convince blindman to use aliases to shorten even the Customers table. And I doubt that blindman can convince Jeff or most other people in this forum not to use aliases. It's a subjective matter.
Agreed .... i think we are just going in circles ....
- Jeff
http://weblogs.sqlteam.com/JeffS
|
|
|
|
cwtriguns2002
Constraint Violating Yak Guru
Philippines
272 Posts |
Posted - 03/28/2007 : 22:22:36
|
quote:
So where, exactly, have I said "never use aliases"?
Of course not. "Customer" sufficiently describes the content of the table. Let me ask you this: Why don't you name your table simply "C" rather than "Customer"?
- As i understand what your quote, Don't use ALIAS 'c', name the table as 'c'.
quote:
And thanks to my avoidance of aliases I don't have to imagine, or guess, or remember, what single-character code represents what table or object.
c for Customer, p for Product, pd for personalData, o for Order,e for employee, a for applicant, etc.... What is hard on this? 
thanks
-Ron- |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 03/29/2007 : 08:27:07
|
Are you going to use these same substitutions every time? With every sproc you write in the database? Are you going to make sure all the developers use the same substitutions?
If not, then every statement you evaluate you will need to check to see whether C stands for "Customer" or stands for "Country" as it might have in the previous query. If you ARE going to enforce this substitution schema across the database then there is no logical reason for not naming your tables with single characters to start with, is there?
Ron, leave this discussion to those who have followed the arguments, can respond intelligently, and who understand terms such as "reductio ad absurdum".
If Jeff chooses to respond again, then I'm happy to keep discussing this with him. If not, then I'm satisfied that we each explained our viewpoints. Either way, I have no more time to reply to nonsense posts.
e4 d5 xd5 Nf6 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 03/29/2007 : 11:51:59
|
one thing is sure: Vijaykumar_Patil seems to know what subjects will spark long threads!
www.elsasoft.org |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/02/2007 : 03:26:10
|
"Trying to figure out which table each column is from drives my nuts"
Well, seeing as how we are consolidating previous threads! my solution to this is to include a mnemonic in the Table names, which is unique to the database, and which is repeated in the Column name. This makes the column names unique too.
We don't prefix a column by table/alias - unless it is ambiguous (i.e. table joined twice)
long table names and lots of joins and expressions in the SELECT, is HARDER to understand with brief, concise and representative table aliases
I'm dyslexic and that's definitely the case for me; thus Jeff's Example One is easier for me to read. But I agree that I have to make assumptions about the aliases, or read the FROM clause first.
This strays from the original performance related question to "readability", which I think also translates into reducing bugs - its important that the code can be quickly and accurately assimilated by the programmer. Consistency is the watchword, of course, and there is plenty of scope for house-style.
This would be my approach
SELECT
cust_Name,
ordh_ID, -- o.OrderID,
ordh_Date, -- o.OrderDate,
ordh_name_ID, -- Assume links to a NAMEs table; was o.OrderedBy,
ordd_Qty, -- d.Qty,
ordd_Rate, -- d.Rate,
[Amt] = ordd_Qty * ordd_Rate, -- d.Qty * d.Rate as Amt,
ordd_Flag, -- d.Flag,
[Status] = CASE WHEN ordd_Flag = 1 AND ordd_Qty = 0
THEN 1
ELSE 0
END
FROM dbo.CUST_Customer
JOIN dbo.ORDH_OrderHeader
ON ordh_cust_ID = cust_ID
JOIN ORDD_OrderDetail
ON ordd_ordh_ID = ordh_ID
WHERE ordh_Date >= '20000101'
AND ordh_Date < '12/31/2000'
AND ordd_Flag = 1
ORDER BY cust_ID,
ordh_Date DESC
Couple of observations:
All keywords in CAPs, indentation changed to the style I use.
[Alias] = Expression
used instead of
Expression AS Alias
because I think that AS Alias may be off the right side of the screen, and thus "missed".
Similarly AND / OR is at the Start of a continuation line, rather than at the End. Makes it more obvious where AND + OR are accidentally mixed without parentheses!
Similarly we never use:
Expression Alias
(i.e. AS omitted) because it encourages a coding style which will fail if a comma is missed.
Footnote: Table names in this example are actually more simple than we use. We actually use a treble-level mnemonic - so Customers and Orders are part of "Sales Order Processing" in my parlance (probably Accounts Receivable in USA-speak??), and they are part of Accounts so we would have names:
Table: ACC_SOP_CUST_Customer
Columns: acc_sop_cust_ID, acc_sop_cust_Name, ...
Part of BlindMan's objection to Aliasing was that the Alias had to be checked against the FROM statement. Also true of my approach, but because the Mnemonic is part of the column name it is enforced and thus always consistent, and therefore something that the programmer becomes familiar with - and the mnemonics are learnt overtime, or when forgotten!, by checking the FROM clause - as we use descriptive names for our tables, but carrying the Mnemonic as well)
Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 04/02/2007 : 09:29:56
|
OK, to throw another log on the fire...how ANSI is it to just ref the table name? You can't do that in DB2 for example, because I actually wanted to do that for generating code/reports...so I had to do:
SELECT Collist FROM myTable99 myTable99...just so it would work
Of course there were many joins involved
It's like everything, you should do it when you have to, and you shouldn't when you shouldn't
In the case of generating code, I had to, when writing sprors on my own I don't have to and to me, there is nothing unclear about it
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
|
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 04/02/2007 : 09:39:10
|
I think we should stop this debate. It is matter of personal preference whether to choose using alias or not.
I am not on either extreme i.e. use alias every time or never use. It depends on the complexity of query and table names. When you feel like using table names is just too much, use meaningful aliases. When table names are short, easy-to-remember and meaningful, use them.
What's the big deal?
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
NeilG
Aged Yak Warrior
United Kingdom
526 Posts |
Posted - 04/02/2007 : 10:26:14
|
| I agree with you there Harsh Athalye, I think that it’s all really down to your own personal preference and which style you are most comfortable working with.a |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/03/2007 : 06:35:22
|
"What's the big deal"
Well ... the OP asked if there were any performance implications of using, or not using, aliases ....
... if you aren't busy you could set up a TEST !!
Kristen |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 04/03/2007 : 07:40:52
|
| FWIW I find the stags-clashing-heads debates certainly the most entertaining and often the most informative of threads. It is always useful to know when good practice is an absolute and when it is an opinion. |
|
|
|
Topic |
|
|
|
Alias yes or no for Performance
