SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Alias yes or no for Performance
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 6

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/28/2007 :  09:53:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/28/2007 :  09:57:26  Show Profile  Reply with Quote
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



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/28/2007 :  10:00:12  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858
Brett -he was talking to me


And if he was, then why post it in a public forum?



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



Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/28/2007 :  10:15:39  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/28/2007 :  11:44:56  Show Profile  Reply with Quote
Jeez....I kinda wished I didn't raise a flippant remark to (re)start an unknown flame war!!!
Go to Top of Page

Gelder
Starting Member

United Kingdom
7 Posts

Posted - 03/28/2007 :  12:01:33  Show Profile  Reply with Quote
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.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/28/2007 :  12:48:48  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/28/2007 :  13:38:57  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/28/2007 :  16:13:43  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36599 Posts

Posted - 03/28/2007 :  17:04:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/28/2007 :  21:04:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

cwtriguns2002
Constraint Violating Yak Guru

Philippines
272 Posts

Posted - 03/28/2007 :  22:22:36  Show Profile  Reply with Quote
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-
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/29/2007 :  08:27:07  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 03/29/2007 :  11:51:59  Show Profile  Visit jezemine's Homepage  Reply with Quote
one thing is sure: Vijaykumar_Patil seems to know what subjects will spark long threads!


www.elsasoft.org
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/02/2007 :  03:26:10  Show Profile  Reply with Quote
"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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/02/2007 :  09:29:56  Show Profile  Reply with Quote
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



Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 04/02/2007 :  09:39:10  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

NeilG
Aged Yak Warrior

United Kingdom
530 Posts

Posted - 04/02/2007 :  10:26:14  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/03/2007 :  06:35:22  Show Profile  Reply with Quote
"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
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/03/2007 :  07:40:52  Show Profile  Reply with Quote
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.
Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000