April 3, 2009 at 3:01 pm
But, done properly,
make the query rather self documenting.
No. NO!!! NO!!!! The table name itself is the most accurate representation of what's being joined. IF, and I say IF, a table alias name would EXPOUND on what subset of the table was being joined, you might be right, but SHOW ME AN EXAMPLE in the wild where that is done. I have NEVER seen that done. NEVER.
It's always "Trades T Inner Join Customers C" or even worse, "Trades A Inner Join Customers B". Just Say No to table aliases! When I'm reading the guts of the join, is C the customers or the clients? Is A the account table or the activity table? I have to mentally resolve the aliases, and leaving them OUT would be 1000% better. I would like to find a tool that would un-alias all of the crap like this for me.
I'm sorry that you've had a bad experience with aliases. I use them in all that I do because my table names tend to be rather descriptive and to include them in the bazillion rather complex queries that I have would make them utterly unreadable. Therefore we have aliases, though only 2-3 characters in length, that are quite specific to each table. I don't believe in using generic aliases (A, B, etc.) because their definition changes for each query. Standardization is the key here.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
April 3, 2009 at 3:02 pm
I have to admit that I hate single-letter table aliases. I've seen them cause more confusion than I've ever seen them resolve. They're just a way for lazy people to save a few keystrokes, in my far-from-humble opinion.
I've seen a cartesian join kill a proc because someone did this:
from Customers C
inner join Calls Ca
on C.ID = C.ID
Took the guy, normally a competent T-SQL coder, half an hour, and then he finally gave up on it and asked me to look at it, and it took me a minute to spot the problem. That piece was in the middle of a six-table, twenty-column select, with a relatively complex Where clause, and it was too easy to overlook, because the mind sees it as "Customers.ID = Calls.ID", not as "Customers.ID = Customers.ID". (In proofreading, that kind of assumption is called "visual dub", and it's really, really easy to do. Our brains are too used to seeing what we expect, instead of what we're actually looking at.)
If the actual table names had been used, or at least meaningful aliases, it would have never happened in the first place, or taken a couple of seconds to spot and resolve, at worst.
On the other hand, I will use table nicknames as aliases, if the table names are too long. Used to have a table named "Entities_Orders_Join", and used to abbreviate it to "EntOrds" as an alias. Enough to be exactly sure which table it was, but a little faster and easier to type. Made a (small) difference since that table was in about half the code in the database. Shorter names, I prefer to type out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2009 at 3:03 pm
GSquared (4/3/2009)
YSLGuru (4/3/2009)
GSquared (4/3/2009)
T-SQL is clunky in certain regards. For example, why doesn't this work:
select Col1, count(*)from dbo.MyTable;
SQL Server will raise an error that Col1 is neither contained in a Group By nor an aggregate. Quite obviously, it is meant to be contained in the Group By, so why not implicitly include it? Sure, that may be a violation of the standard, but in my opinion, the standard is flawed on this point.
I mean no disrespect but I do hope you are joking about why GROUP BYs arein't implicit. While in your example you could say that the logical Grouping is on Col1, not every Query with 1 or more aggregates in it is like this. When you have more then 1 aggregate and one non-aggregated value the order of the GROUP BY is very important. Take these 2 query's for example:
/*Query #1*/
SELECT col1, Col2, Count(col3)
FROM dbo.TABLE1
GROUP BY col1, col2
/*Query #2*/
SELECT col1, Col2, Count(col3)
FROM dbo.TABLE1
GROUP BY col2, col1
The resulting set will not always be the same between the 2 depending on the data in TABLE1. This is why a GROUP BY is required and not implicit.
Trust me on this one: I'm very familiar with how Group By works. I have more than a little familiarity with T-SQL.
At the same time, I think an implicit Group By, taking the same non-aggregate columns as the Select clause, in the same sequence, would be handy in certain circumstances.
There are a number of other things of a similar nature that I would like to have, which, if misused, would be bad, but if used correctly, would be nice. Not necessary, not critical, not even important, just "nice".
Yes, that opens the door to misusing these things. Considering all the harm that can already be done by people who don't pay attention to how "or" and "and" work in Where clauses, don't understand that cursors are generally a bad idea, don't understand how to avoid RBAR, don't understand proper indexing, don't understand normalization, don't understand denormalization, don't understand data modeling, don't understand data types, et al, ad naseum, I'm not worried about a few minor problems with these things. I just think they would be handy.
So, no, I'm not kidding.
I don't think many would disagree that it woiuld be nice if in T-SQL there was an easy way to have an implicit Auto-GROUP BY I'd bet that the reason something like this hasn't yet been added as some new feature is due to some technical/mechanical reason in how the engine process queries with Grouping in them. Perhaps for the same or similiar reason that SP's & UDF's have their own limitations.
Perhaps something like the below would serves as compromise where in you could get an auto-group by without having to explcitly spell out every column (a second time since they are already liwsted in the SELECT) but still explcitly tell the engine to group by in a specific order:
SELECT Col1, Col2, Col3, COunt(Col4)
FROM dbo.MYTABLE
GROUP BY ALL
Here the engine would by default group from left to right ALL items not in an aggregate. This could certainly be handy
Kindest Regards,
Just say No to Facebook!April 3, 2009 at 3:26 pm
Aaron N. Cutshall (4/3/2009)
But, done properly,
make the query rather self documenting.
No. NO!!! NO!!!! The table name itself is the most accurate representation of what's being joined. IF, and I say IF, a table alias name would EXPOUND on what subset of the table was being joined, you might be right, but SHOW ME AN EXAMPLE in the wild where that is done. I have NEVER seen that done. NEVER.
It's always "Trades T Inner Join Customers C" or even worse, "Trades A Inner Join Customers B". Just Say No to table aliases! When I'm reading the guts of the join, is C the customers or the clients? Is A the account table or the activity table? I have to mentally resolve the aliases, and leaving them OUT would be 1000% better. I would like to find a tool that would un-alias all of the crap like this for me.
I'm sorry that you've had a bad experience with aliases. I use them in all that I do because my table names tend to be rather descriptive and to include them in the bazillion rather complex queries that I have would make them utterly unreadable. Therefore we have aliases, though only 2-3 characters in length, that are quite specific to each table. I don't believe in using generic aliases (A, B, etc.) because their definition changes for each query. Standardization is the key here.
If one table is called Trades, and another is called Transactions, then there's no reason to alias either of them. People ONLY see examples that use aliases, and they think it's a required part of the syntax. That's too bad.
I think you're wrong about the "unreadable" part. The way you do it, there's one set of long, desctiptive names, and there's a second set of 2 or 3 character aliases, and we humans have to parse all of that and join the cryptic names to the descriptive names in our head before we can START to understand what's going on.
Long, descriptive names do NOT make SQL statements unreadable. Far from it. Aliases often make SQL statements incomprehensible! It's not just that I have had "some bad experiences" with aliases, but they are not helpful. (I know they are occasionally required, like with self-joins and derived tables, but 99.995% of the time, they are NOT necessary and the query would be easier for humans to read without them.)
I hear people say they can't type those long table names. For those people who can't type, ctrl-C and ctrl-V is your friend. Copy and past the table names. Readability is more important than ease of writing. (How much time do you TRULY spend actually TYPING IN the SQL statements? So what if it takes 40 seconds instead of 5 seconds to write a join statement? Think of it as an investment in the future readability of your code.)
Although this is off the original topic, I still haven't seen a SQL statement that was easier to read with the aliases than without them. Please show me some, if you have any.
April 3, 2009 at 3:32 pm
YSLGuru (4/3/2009)...
Perhaps something like the below would serves as compromise where in you could get an auto-group by without having to explcitly spell out every column (a second time since they are already liwsted in the SELECT) but still explcitly tell the engine to group by in a specific order:
SELECT Col1, Col2, Col3, COunt(Col4)FROM dbo.MYTABLE
GROUP BY ALL
Here the engine would by default group from left to right ALL items not in an aggregate. This could certainly be handy
Especially when you have extensive logic, i.e. case or calcs, in col1,2,3...
😎 Kate The Great :w00t:
If you don't have time to do it right the first time, where will you find time to do it again?
April 3, 2009 at 3:34 pm
Are table aliases really that hard to decipher? Even if you had a query with 6 joined tables and every one of them using a single character table alias, all that's required to know which table is being used is to look in the FROM clause.
April 3, 2009 at 3:39 pm
Timothy (4/3/2009)
Are table aliases really that hard to decipher? Even if you had a query with 6 joined tables and every one of them using a single character table alias, all that's required to know which table is being used is to look in the FROM clause.
Right. And pair up the aliases with the real table names. In our head. While we're also trying to keep track of which columns are joined, and what the overall statement is doing. All of which is easier if the table names are not aliased; it's one less mental step.
Why put anyone through the extra effort to decipher, even if it's small? Why are so many people wedded to table aliases? The From statement might be "TableA Inner Join TableB on condition1 and condition2 Inner Join TableC On Condition 3 and Condition4 Left Outer Join TableD on Condition5" broken up on several lines, of course. The From statement can be a long statement.
They are unnecessary and they make the code much harder, or slightly harder, to read. Either way.
David
edited to fix conditions
April 3, 2009 at 3:40 pm
I prefer full tables names so I don't have to go look up the from clause while digging in the WHERE or the sub-selects.
Aliases are needed in subselects if the same tables are re-used, but I usually define the inner for it's purpose along with the table name.
Non-descriptive aliases are just plain lazy. Any kind of code should be as self documenting as possible, and the origin table names should have been descriptive of what they contain, not non-descript acronyms (where possible which is 99.9% of the time).
April 3, 2009 at 3:42 pm
Michael Valentine Jones (4/2/2009)
I think that SQL as a language is not really the problem. There are a lot of developers out there that really don’t do well in any language, but the quality of their work is more exposed in SQL where performance and accuracy is more of an issue.The number one problem that most developers have when working with SQL Server is not using the language, but developing a schema that models the data accurately and completely. Deficiencies in the design of the data are much harder to fix once they make it into a production environment, and they make it hard for developers to write fast, efficient queries to get the data they need. At the same time, many developers seem unaware of the long term implications of bad logical and physical designs, so the most important aspect of database development gets the least amount of attention. Bad design leads to developers spending a lot of time trying to stand SQL on its head to do things it shouldn’t have to do with a well designed schema.
I remember a conversation with a developer where I questioned the fact that he used inappropriate data types and that none of his tables had a primary key. He dismissed this saying that he was designing for the “real world”, and couldn’t be bothered with things were of only “theoretical interest”.
This sounds like the (St.) Valentine's (day) massacre of developers. There's nothing wrong with sql. The problem is the dumb and stupid developers using it! The trash the user defense. I could just as easily employ the same sophistry to sql users avoidance of dataphor. Their just to stupid and hard headed to understand the difference. But do you see me doing such childish things? 🙂
best,
steve
April 3, 2009 at 3:50 pm
dphillips (4/3/2009)
I concur with the previous post, and would also add that the IDE's for T-SQL or SQL in general could do with a lot more features.SQL is still largely invented by the practitioner, instead of the science.
The science comment is a mouth full my friend 🙂 But how do you introduce science to sql? What does this science look like? Where does it come from and where does it take us? This is what I'm trying to get at by introducing sql users to Dataphor! What types of science do you see in BOL? 🙂
April 3, 2009 at 3:54 pm
David Walker (4/3/2009)
Why put anyone through the extra effort to decipher, even if it's small? Why are so many people wedded to table aliases? The From statement might be "TableA inner join TableB on . The From statement is a long statement.
I use table aliases because they're easier to type throughout the query. I guess I just don't get the fuss about them since I've had no problem deciphering queries that used them. If you have a really long query you can just format the code to make it easier to understand, put every table in the FROM clause on a separate line; that's what I do, makes it super easy to see what's going on. It's all preference though I guess. You might think a query is more readable by having a full table name repeated everywhere one of its columns is used, but not me, and apparently not the people like me.
April 3, 2009 at 4:03 pm
tiffany.johnston (4/3/2009)
While T-Sql was frustrating to me at first because of the lack of intellisense (which I probably rely on too much in my C# coding 🙂 )
No, no, no, absolutely not. Intellisense and the whole IDE are great power tools. You don't hear a lumberjack say, "I rely on my chainsaw too much; I should really use that double-bit axe more." Or a farmer say, "I use my tractor to plow fields with way too much, I should get some draft horses." In 1999 I went from writing VB6 to two years writing Java without a useful IDE or debugger (we had Symantec instead), and I really missed that VB6. All the other codemonkeys would scoff at how unmanly VB was because of the IDE, and how it took a real programmer to write Java. I found it a pain. Dock me programmer-points, but give me Intellisense.
April 3, 2009 at 4:03 pm
kevriley (4/3/2009)
dphillips (4/3/2009)
I concur with the previous post, and would also add that the IDE's for T-SQL or SQL in general could do with a lot more features.dphillips, like what?
Kev
Some items similar to what jfox posted on this answer. Mostly, SQL has been around for over 20 years now.
- It is about time it had some intellisense (I know there are 3rd party tools and 2008 has it. So I am happy about this.
- Design-time error flagging when a function or command is unknown.
- Suggestions hints for deprecated items, and other less performant structures.
I am a Legos guy. I know what each piece does. I know how to make and/or mod the parts if needed. But in the end, when it is time to build something, I don't want to spend 3 or 4 compile runs to find all my fat-fingered typos or other problems, especially in very lengthy code.
I do not really care how hard it used to be in other languages. I've been there... COBOL, FORTRAN, Turbo Pascal, several raw coded scripting languages... they all tanked for lack of an intelligent IDE.
I also know that .Net now provides, or can be made to provide some of this capability (as can Eclipse), just like a pluggin for PHP was made for the .NET IDE. I am looking forward to also having these kinds of features in the management IDE as we upgrade to 2008 or higher.
I understand the original editorial was speaking rather to the form and function of the language, not the dev environ, so my comments up to this point do take a back seat. I will take the stance of GSquared on this issue and say that I'd rather that MS keep devoting the time to internal speed, optimization, and scalability features, rather than my minor desires here.
As to the language itself, I like it the way it is. It is explicit as data handling must be. Could use a few more built in functions, but I see those coming down the road. I'm happy with that.
April 3, 2009 at 4:22 pm
Dave (4/3/2009)
tiffany.johnston (4/3/2009)
While T-Sql was frustrating to me at first because of the lack of intellisense (which I probably rely on too much in my C# coding 🙂 )No, no, no, absolutely not. Intellisense and the whole IDE are great power tools. You don't hear a lumberjack say, "I rely on my chainsaw too much; I should really use that double-bit axe more." Or a farmer say, "I use my tractor to plow fields with way too much, I should get some draft horses." In 1999 I went from writing VB6 to two years writing Java without a useful IDE or debugger (we had Symantec instead), and I really missed that VB6. All the other codemonkeys would scoff at how unmanly VB was because of the IDE, and how it took a real programmer to write Java. I found it a pain. Dock me programmer-points, but give me Intellisense.
Here! Here!
You'll find that those purported manly raw coders also spend an inordinate amount of time debugging and debunking their code. And 99% of those never have used both sufficiently to known the difference, but will give heat anyway.
Ask them why Eclipse has become such a big deal to the open-source world? I have been coding in both worlds for over a decade.
Breakout the super-buzz 3000 remote-controlled combination chain saw/sectioner/log-splitter, with the autoloader conveyor! In otherwords: why bash the bigger-better-faster just because one once had it harder? Some things don't have to be that hard.
In any case, T-SQL as a language is just fine for the purpose, has a high degree of compatibility and portability to other forms of SQL, with regular additions that for the most part seem (to me at least) timely and proper.
April 3, 2009 at 5:18 pm
dphillips (4/3/2009)
I am a Legos guy. I know what each piece does. I know how to make and/or mod the parts if needed. But in the end, when it is time to build something, I don't want to spend 3 or 4 compile runs to find all my fat-fingered typos or other problems, especially in very lengthy code.
Heh... I don't have those problems even on a fresh build... click'n'drag from the object browser works just fine for me, I know how to type, and I test as I go which has kept me from ever needing to use an (ugh!) debugger. Intellisense actually slows me down because it get's in the way. It's one of the things I hated about TOAD.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 266 total)
You must be logged in to reply to this topic. Login to reply