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/27/2007 :  16:00:47  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
This is silly. Anyone who thinks that using short, concise meaningful aliases to keep a sql statement short and easily readable is a bad idea simply isn't much of a developer. You may be a great DBA, capable of wonderful back up strategies and maybe even good at data modeling, but it shows a clear lack of understanding the huge advantages of writing clear, concise code that is easy to read and maintain.

that is like saying "Using classes in C++ is bad. They induce overhead and often are used wrong, resulting in poor, inefficient and confusing code. Therefore, never use classes in C++."





- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 03/27/2007 :  16:14:08  Show Profile  Reply with Quote
Sometimes the only way to get a guy who is too in love with his idea to give it up, is to bash him over the head with statistics. Fortunately, most people who are that inflexible don't know that statistics can prove almost anything.
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

Denmark
384 Posts

Posted - 03/27/2007 :  16:39:03  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
quote:
"Using classes in C++ is bad. They induce overhead and often are used wrong, resulting in poor, inefficient and confusing code. Therefore, never use classes in C++."


hahahahah sniff sniff ... you made my day.

P.S. I have a co-woker that dont like aliases either, and even put every thing in [] which really helps readability, but as someone here used to say (or still do) "if it was hard to write the it should be hard to write..."

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/27/2007 :  16:52:40  Show Profile  Reply with Quote
Then, of course, you have to use an alias if your select is referencing the same table more than once.

I have no problem with developers with using either TableName.ColumnName or Alias.ColumnName in queries, but I insist that they use either one or the other.

What I hate is a query with 8 tables in a join and none of the column names qualified with the table name or alias. Trying to figure out which table each column is from drives my nuts. In any case, I QA reject any code I see written that way, because it can break if a column with the same name is added to a table later.








CODO ERGO SUM
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/27/2007 :  17:54:34  Show Profile  Reply with Quote
My opinion is that if developers want to use aliasing/renaming to increase code readability or whatever,
they may do it in the business layer that abstracts the database.

I am seriously allergic to having different names dereferencing exactly the same database object.
It's a road to confusion, not clarity.

For performance, the argument is moot, and I can't see what difference it might make, except for the few dereferencing cpu cycles needed when resolving the alias.

rockmoose
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  18:22:13  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Are we talking about creating views that can be used as "shorthand" for tables, like:

create view x as select * from SomeLongTableName

and then sometimes referring to x and other times SomeLongTableName throughout the database code? Or as we talking about, in a specific SQL statement, giving a table an temporary alias?

I can only hope that maybe I am misunderstanding the question and people are debating the first thing I mentioned, in which case it makes sense that you would not want to do that. As for not aliasing a table in a SELECT ... what damage does that do again? Please give an example. And remember, we are not talking about ways that aliases can be abused. (i.e, alias Customers as DKSHJFV or something like that) we are debating whether or not, as a practice, table aliases should be used in SELECT's. Please give an example in which a complicated select, with long table names and lots of joins and expressions in the SELECT, is HARDER to understand with brief, concise and representative table aliases.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/27/2007 :  18:26:56  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

This is silly. Anyone who thinks that using short, concise meaningful aliases to keep a sql statement short and easily readable is a bad idea simply isn't much of a developer. You may be a great DBA, capable of wonderful back up strategies and maybe even good at data modeling, but it shows a clear lack of understanding the huge advantages of writing clear, concise code that is easy to read and maintain.

That's a rather personal attack on my qualifications as a SQL developer, and literally hundreds of people that I have helped through this and other forums would happily disagree with you. So thankfully, your opinion isn't worth pig snot.

Gratuitous use of aliases by definition reduces the information content of each line in a SQL statement. Reducing information content is seldom a good thing. "A.Description" simply is not as informative as "Accounts.Description", and anybody that thinks otherwise is a complete knee-biter (in my personal opinion, of course).

"Concise" does not always mean "Clear", and often means the opposite. I happen to think clarity trumps brevity hands-down, and the compiler does not care either way.

My most complex SQL code is both clear and easy to maintain BECAUSE I avoid using aliases, and also because I take the time to properly format it.

Your coding shortcut is the next DBA's debugging nightmare.

e4 d5 xd5 Nf6
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  18:30:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
blindman, I didn't mean that as an attack, sorry, but there is a difference between a DBA and a developer. I am not a DBA, and I don't claim to be as good at administration as a DBA. By the same token, I expect that my pure T-SQL skills are probably stronger than most DBA's since that's pretty much all I do with SQL Server as opposed to administrative tasks, as well as developing on other programming languages. There's definitely a difference in the way DBA's and developers approach things, as there should be.

EDIT -- i just re-read my post and I realized I really wrote it poorly -it was definitely offensive and I did not mean to write it the way I did; I meant it in a general sense regarding DBA's and developers, and did not mean to imply that you were a poor developer if that's what you primarily do. I completely apologize for the way it came off, I really did not mean it personally towards you ... It just surprised me that someone in a developer role wouldn't understand the value of meaningfully named pointers to refer to objects as opposed to fully qualifying things over and over, it's kind of a core programming concept...end of edit

You may have missed my last post -- how about an example of where using aliases intelligently for some reason makes the code harder to understand or maintain.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 03/27/2007 18:55:54
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/27/2007 :  18:34:24  Show Profile  Reply with Quote
I was talking about the CREATE SYNONYM (SQL 2005).
For a moment I thought the syntax was ...create alias...

If we are "just" talking about aliasing table names in TSQL, then I am all for it, and encourage it, strongly.
And all columns should be qualified with the table name/or/alias in any query.

rockmoose
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  18:34:46  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>"A.Description" simply is not as informative as "Accounts.Description", and anybody that thinks otherwise is a complete knee-biter (in my personal opinion, of course).

By that same token, you'd have to say that

System.Web.UI.WebControls.DropDownList

is much more informative than

DropDownList

but to fully qualify it over and over, again, makes your code harder to read and more error prone. If you write any apps in .NET or JAVA or a similiar language, do you always fully qualify every single class that you ever use from the root namespace?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  18:35:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by rockmoose

I was talking about the CREATE SYNONYM (SQL 2005).
For a moment I thought the syntax was ...create alias...

If we are "just" talking about aliasing table names in TSQL, then I am all for it, and encourage it, strongly.
And all columns should be qualified with the table name/or/alias in any query.

rockmoose



Ah! Glad I asked. Thanks for clarifying, Rockmoose, I figured something was getting lost in translation somewhere ....

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 03/27/2007 :  18:54:29  Show Profile  Visit jezemine's Homepage  Reply with Quote
perhaps we should do away with pronouns in the english langauage too. :)

after all, pronouns are aliases for nouns, and can cause confusion.


www.elsasoft.org
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/27/2007 :  18:57:24  Show Profile  Reply with Quote
In the same vein shorter code is more easily read.
Up to a point of course...

It's good to be explicit, but to be concise is also good, either one driven to the extreme is absurd.

And which is fastest?
Customer AS c
Customer AS cus
must be a nobrainer, it is faster to parse 1 char than 3 char

In practice I quite often encounter multi-table joins, and EVEN self-joins (omg!)
Consistently giving the tables short, concise, appropriate aliases does help me in keeping the code readable, maintainable and understandable.
I don't have to spell out every letter.

rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 03/27/2007 :  19:08:46  Show Profile  Reply with Quote
quote:
Originally posted by jezemine

perhaps we should do away with pronouns in the english langauage too. :)

after all, pronouns are aliases for nouns, and can cause confusion.


www.elsasoft.org



That's funny!

The best logical systems are the ones with few basic elements,
from which to build.
Language may be simplified, but just up to the point where information is lost.
But dare we do that, in light of that we might not be aware of what information is removed.

rockmoose
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 03/27/2007 :  19:25:46  Show Profile  Visit jezemine's Homepage  Reply with Quote
reminds me of one of Einstein's quotes:

"Make everything as simple as possible, but not simpler."

another one I like:

"All coding problems can be solved with another layer of indirection, except too many layers of indirection."


www.elsasoft.org
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/27/2007 :  20:16:21  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858



jsmith,

Apology accepted. I figured that post was tossed off rather quickly and was not typical.

But I am primarily a developer. Not an administrator. If you review my posts on sqlteam.com or dbforums you will see that almost all of them pertain to database design and sql coding.

quote:
Originally posted by jsmith8858how about an example of where using aliases intelligently for some reason makes the code harder to understand or maintain.

How about an example where reducing useful information makes something easier to understand?

quote:
Originally posted by jsmith8858By that same token, you'd have to say that System.Web.UI.WebControls.DropDownList
is much more informative than DropDownList

Yes. It is more informative.

quote:
Originally posted by jsmith8858but to fully qualify it over and over, again, makes your code harder to read and more error prone.

No, it does not make it more error prone. But I have to point out that all the examples given so far to support use of aliases in SQL code have NOT BEEN SQL CODE!
But even in the poor analogy you cite, it is as obviously unnecessary to fully qualify every single class from the root namespace as it is to fully qualify a database object name with database and owner. Since the database is constant and the (default) owner can usually be assumed to be dbo, fully qualifying in this case DOES NOT ADD ANY HELPFUL INFORMATION to the code. And that is the key. Aliasing, particular the sloppy use of single character aliases, reduces the information content of the code. Show me an example where it does not. And that inevitably makes debugging more difficult for someone who was not the original developer, and who is saddled with the dual tasks of deciphering logic and simultaneously deciphering the references.


e4 d5 xd5 Nf6
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/27/2007 :  21:25:16  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I want you to answer this honestly. Do you really feel that this:

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


is *harder* to read and/or edit and work with, rather than :


select
	Customers.Name,
	CustomerOrders.OrderID,
	CustomerOrders.OrderDate,
	CustomerOrders.OrderedBy,
	CustomerOrderDetails.Qty,
	CustomerOrderDetails.Rate,
	CustomerOrderDetails.Qty * CustomerOrderDetails.Rate as Amt,
	CustomerOrderDetails.Flag,
        case when CustomerOrderDetails.Flag = 1 and CustomerOrderDetails.Qty = 0 then 
          1 else 0 end as Status
from
        Customers 
inner join
        CustomerOrders on Customers.CustomerID = CustomerOrders.CustomerID
inner join
        CustomerOrdersDetailts on CustomerOrdersDetails.OrderID = CustomerORders.OrderID
where
        CustomerOrders.OrderDate >= '1/1/2000' and CustomerOrders.OrderDate < '12/31/2000' and
        CustomerOrderDetails.Flag = 1
order by
        Customers.CustomerID,
        CustomerOrders.OrderDate DESC


??

And this is a very simple SQL statement. Imagine something more convoluted! The 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.

All of your examples about why aliases are bad seem to revolve around "using sloppy aliases". If your point is "poorly named aliases are bad" then we all agree. But that is not the point you've been stating. I 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.

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

Do you name your tables like "CustomerNameAndNumberAndStatusAndType" ? Why not? It is very meaningful and descriptive. The table names gives you lots of great detail that you lose if you just call it "Customers" or something like that. After all, isn't a table name just an alias for the table object itself? The world of programming is full with example upon example of this practice; to somehow think it doesn't apply to an especially verbose language such as SQL is simply wrong.

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

edit ... to fix MANY typos!

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 03/27/2007 21:33:01
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 03/27/2007 :  22:26:57  Show Profile  Visit jezemine's Homepage  Reply with Quote
Jeff: good example. I don't want to have to search for the column names, but in your second example that's what I have to do. they are buried.

blindman: 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!

Jesse




www.elsasoft.org
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2144 Posts

Posted - 03/28/2007 :  07:17:52  Show Profile  Reply with Quote
MVJ said
quote:
drives my nuts


Where to? A local restaurant? Or like a nail?


[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

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

And I still disagree with you on this point.



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
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.41 seconds. Powered By: Snitz Forums 2000