June 17, 2011 at 1:45 pm
JustMarie (6/17/2011)
Phil Parkin (6/17/2011)
If you write a lot of T-SQL, consider doing yourself a favour and omit the readability words (AS is one) - it soon becomes second nature and as AS has no technical function, has no impact.I'm trying to wrap my head around why you would exclude readability words in your code. Personally I find them darn helpful when working with multiple table joins so I can find the darn alias names.
I would think that making code MORE readable rather than less would be a goal since it helps whoever has to maintain it in the future.
My opinion, for what it's worth.
Yeah, it varies from person to person. To me, code without a bunch of AS's is more readable... but I do still use INNER JOIN when I could just use JOIN, so it's not an absolute. Granted, that's not exactly the same thing, but it's similar.
A better example might be :
ROLLBACK
ROLLBACK TRAN
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION TransactionName
June 17, 2011 at 4:37 pm
Slightly off-topic, but related to your reason for the original post....
You might like to try this : http://www.atlantis-interactive.co.uk/products/sqleverywhere/default.aspx
I am not affiliated - it is just a free tool for writing queries with a lot of nice features, including tool tips when you hover your mouse pointer over an identifier that detail the source of the identifier.
For this type of clean up job it can be really useful.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 17, 2011 at 6:22 pm
jmaloney-1117255 (6/17/2011)
Steve Hoyer (6/17/2011)
I'm working on a query that's returning about 45 rows and most of the rows are combinations of other fields in one way or another, so most of them are aliased. It got me thinking though, is there any difference between aliasing fields in these two ways:(field1 + field2) AS alias1
alias1 = (field1 + field2)
I question this because from a readability stand point, it seems like having all the alias names front and center, lined up neatly, would make it much easier to read.
So, in summary, is there is a practical difference to using AS instead of = when aliasing column names?
tia,
Steve.
I use the 2nd technique ([alias] = ) not just for superior readability, but also because if you forget a comma using the first technique, you've just inadvertently aliased a column with the name of another column.
An author may have intended to write
Select col1, col2but missed a comma, resulting in col1 aliased as col2, and missing col2 completely.
Ex. -
Select col1 col2which is equivalent to
Select col1 AS col2... but not quite as clear.
HTH:-)
Bingo. That's one of several reasons I prefer the Alias = Column/Expression method.
The other thing is that the method puts the alias in the same spot for me... character column 9 which makes it easier to read for me.
Last but not least, I'll usually so a SELECT in preparation for doing an UPDATE and the method means I don't have to touch the SELECT list to convert it from a SELECT to an UPDATE.
Again, as others have already stated... it's a personal preference that I wouldn't levy on anyone. It does make for some mighty pretty and easy to read code, though. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2011 at 6:02 am
Phil Parkin (6/17/2011)
If you write a lot of T-SQL, consider doing yourself a favour and omit the readability words (AS is one) - it soon becomes second nature and as AS has no technical function, has no impact.
It helps in debugging complex queries. Especially if you use the aliases elsewhere in the query.
- 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
June 20, 2011 at 6:45 am
GSquared (6/20/2011)
Phil Parkin (6/17/2011)
If you write a lot of T-SQL, consider doing yourself a favour and omit the readability words (AS is one) - it soon becomes second nature and as AS has no technical function, has no impact.It helps in debugging complex queries. Especially if you use the aliases elsewhere in the query.
I think you misunderstood my point, which, admittedly, was not written very clearly.
Here's another attempt: 🙂
Removing the word 'AS' from between a column name and its alias has no technical impact, because the SQL query processing engine ignores the word. The word is there purely to assist those humans who find it easier to read queries which contain it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2011 at 6:16 am
Phil Parkin (6/20/2011)
GSquared (6/20/2011)
Phil Parkin (6/17/2011)
If you write a lot of T-SQL, consider doing yourself a favour and omit the readability words (AS is one) - it soon becomes second nature and as AS has no technical function, has no impact.It helps in debugging complex queries. Especially if you use the aliases elsewhere in the query.
I think you misunderstood my point, which, admittedly, was not written very clearly.
Here's another attempt: 🙂
Removing the word 'AS' from between a column name and its alias has no technical impact, because the SQL query processing engine ignores the word. The word is there purely to assist those humans who find it easier to read queries which contain it.
No, I read your message correctly. What I disagree with is "...consider doing yourself a favor and omit the readability word...". Yes, it's only in there to make the written code easier to read, and it's stripped out, along with comments, before the actual compilation takes place in the engine. Readability in the written version, however, is an important factor. Leaving out "AS", because it has no mechanical value, is the same as "don't use comments to document your code, regardless of the fact that doing so will save you time and effort in the future". Either one saves (at most) a few seconds right now, at the cost of minutes, hours or days in the future. It's a "buy high, sell low" proposition, on time instead of money.
- 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
June 21, 2011 at 6:59 am
No, I read your message correctly. What I disagree with is "...consider doing yourself a favor and omit the readability word...". Yes, it's only in there to make the written code easier to read, and it's stripped out, along with comments, before the actual compilation takes place in the engine. Readability in the written version, however, is an important factor. Leaving out "AS", because it has no mechanical value, is the same as "don't use comments to document your code, regardless of the fact that doing so will save you time and effort in the future". Either one saves (at most) a few seconds right now, at the cost of minutes, hours or days in the future. It's a "buy high, sell low" proposition, on time instead of money.
In this, we shall have to agree to disagree on almost every point.
First, you suggest that including the AS's makes the code easier to read. I find it harder to read and I know I am not alone. Would you also prefer:
Give me a list of all of our female customers in order of surname, in reverse alphabetic order
over
Select * from cust where sex = 'F' order by surname desc?
I also disagree with your likening the exception of AS to the exception of comments. It's a totally false comparison, because no information is lost by the omission of AS's.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2011 at 8:20 am
Phil Parkin (6/21/2011)
No, I read your message correctly. What I disagree with is "...consider doing yourself a favor and omit the readability word...". Yes, it's only in there to make the written code easier to read, and it's stripped out, along with comments, before the actual compilation takes place in the engine. Readability in the written version, however, is an important factor. Leaving out "AS", because it has no mechanical value, is the same as "don't use comments to document your code, regardless of the fact that doing so will save you time and effort in the future". Either one saves (at most) a few seconds right now, at the cost of minutes, hours or days in the future. It's a "buy high, sell low" proposition, on time instead of money.
In this, we shall have to agree to disagree on almost every point.
First, you suggest that including the AS's makes the code easier to read. I find it harder to read and I know I am not alone. Would you also prefer:
Give me a list of all of our female customers in order of surname, in reverse alphabetic order
over
Select * from cust where sex = 'F' order by surname desc?
I also disagree with your likening the exception of AS to the exception of comments. It's a totally false comparison, because no information is lost by the omission of AS's.
You asserted it was okay to ommit readability because it's got no technical function in the post I replied to. I'm pointing out that that's specious.
Yes, leave out "AS" if you find it actually reduces readability for you. My assertion is simply, don't justify that decision by stating that the engine strips it out. Make the assertion simply as an opinion, don't try to back it up and justify it by the further statement you originally included.
It's definitely not valid that readability can be sacrificed "because the engine strips that out anyway", which is what you asserted in the second half of your statement.
On the point of removing "AS" improving readability, I can't speak for your specific case, because I'm not you, but the assertion flies in the face of hundreds of years of study on the subject of readability and typography and layout. There's a tremendous amount of information on the subject. It is, of course, all generalized into rules that work the best for the majority of humanity, and "dead-weight words" (also called "noise words") like "as", used to make relationships explicit instead of implicit, do add to the word-count, but also add significantly to speed of comprehension, in every study that's ever been done on the subject. Research it a bit, you'll find boatloads of material. The only reason I know it is because I managed a typsetting and editing office for several years in the 90s, and I had to know it for that. So, while I'm sure you're right about readability for you, I would need to see a study that proves it improves readability for most people, as that contradicts all information I've ever studied on the subject.
- 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
June 21, 2011 at 8:46 am
You asserted it was okay to ommit readability because it's got no technical function in the post I replied to. I'm pointing out that that's specious.
You continue to infer things from my posts which I did not write nor intend. All I intended to suggest was to leave out readability words to reduce typing. What I did not say was 'make your code as terse and obtuse as possible', or anything similar, as you appear to be suggesting.
Yes, leave out "AS" if you find it actually reduces readability for you. My assertion is simply, don't justify that decision by stating that the engine strips it out. Make the assertion simply as an opinion, don't try to back it up and justify it by the further statement you originally included.
As above. My meaning was that the engine removes the words, so therefore you can be confident that removing them, or not using them, will not have an effect on the results of the queries when they are executed. Nothing more.
It's definitely not valid that readability can be sacrificed "because the engine strips that out anyway", which is what you asserted in the second half of your statement.
No I didn't. I was talking about readability words, not readability. There is a distinct difference.
On the point of removing "AS" improving readability, I can't speak for your specific case, because I'm not you, but the assertion flies in the face of hundreds of years of study on the subject of readability and typography and layout. There's a tremendous amount of information on the subject. It is, of course, all generalized into rules that work the best for the majority of humanity, and "dead-weight words" (also called "noise words") like "as", used to make relationships explicit instead of implicit, do add to the word-count, but also add significantly to speed of comprehension, in every study that's ever been done on the subject. Research it a bit, you'll find boatloads of material. The only reason I know it is because I managed a typsetting and editing office for several years in the 90s, and I had to know it for that. So, while I'm sure you're right about readability for you, I would need to see a study that proves it improves readability for most people, as that contradicts all information I've ever studied on the subject.
I cannot believe that these hundreds of years of study are directly applicable to programming languages, which I hope you'll concede are different from natural language. Therefore the research cannot be considered directly applicable.
Ask a C# programmer why they prefer C# over VB and one of the reasons will be that it requires fewer words of source code to achieve the same result. VB programmers are likely to find the more natural and forgiving language of VB preferable. But the fact that VB is more readable does not mean that it should replace C#.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2011 at 8:57 am
Only reply I can give is "read your own statement". Read it as if someone else wrote it, without any internal thoughts that weren't in the words. I'm not adding any words or concepts to it. I'm reply to exactly what you wrote, nothing more, nothing less.
- 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
June 21, 2011 at 10:52 am
What I did learn today; you can use a string literal to do an alias, but you probably shouldn't because someday it will be deprecated.
From my experience, it is a matter of user preference, and not necessarily yours; which determines how you do an alias.
I generally use the Column (AS) Alias but will change to the Alias = method when dealing with CASE statements as I find that more readable. I also try to keep the alias methods homogeneous within a transaction.
As for this...
Would you also prefer:
Give me a list of all of our female customers in order of surname, in reverse alphabetic order
over
Select * from cust where sex = 'F' order by surname desc?
I am a firm believer in give them what they want/need, but never give everything due to laziness. That wonderful * I have learned can be certain doom when people stuff files into tables, or you are dealing with an linked XLS which gives you the columns in alphabetical order
June 21, 2011 at 12:27 pm
My 2 cents. I find it easier to read column aliases with AS - i.e. Column1 AS NewColumnName. However, for some reason, I find table aliases easier to read without the AS. Kind of strange?
Todd Fifield
June 22, 2011 at 8:06 am
tfifield (6/21/2011)
My 2 cents. I find it easier to read column aliases with AS - i.e. Column1 AS NewColumnName. However, for some reason, I find table aliases easier to read without the AS. Kind of strange?Todd Fifield
Almost certainly a question of trained habits. General readability is not necessarily equal to individual readability, because the brain and eye can learn habits over time that do all kinds of odd and interesting things to perception.
I know of an expirment that was done where they had 6 "subjects" in a room, and the researcher had flash-cards with colors on them. He would show a card to the group, point at one of the subjects, and that subject would name the color. After everyone got comfortable with this, he showed a yellow card, and the subject said it was red. Most of the group didn't react to this. A few cards later, all correctly named, he showed that same yellow card to another subject, and again it was "red". When he eventually showed the card to the final subject, he also said it was "red". What that last guy didn't know is that all of the other subjects had been coached before-hand to call that card "red", to see if the uncoached guy would follow suit and call an obviously yellow card "red" in order to stay in agreement with the majority of the group. He did. To this day, he calls that particular shade of yellow "red" unless he's paying specific attention to it.
You can train people to do the most amazing things with perception, like that, and readability is part of that. Thus, typography has to comply with both general principles of what the untrained brain has the most comfort with, and what a particular population is indoctrinated into. Whitespace, for example, is a "general" rule, because the untrained mind prefers lots of it, but it's possible to train people to dislike it.
Hence, some people, due to habit and indoctrination, will prefer leaving out or altering some of the usual principles of layout and typesetting, as with the discussion about leaving out "AS". The human mind also tends to assume that others think the same way as self, and tends to reject data that violates personal preferences, so the discussion on that point got a little off-rail. Neither factor makes anyone "wrong" or anyone else "right" on specific, personal preferences.
- 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
June 22, 2011 at 9:34 am
Good call with the Trained Habits.
I can relate very much to this as I do most of TSQL by hand (in Sql Query Analyzer) and my coworkers (who are more C# and vbScript) generally use the tools to generate their code in Enterprise Manager, Management Studio, or Visual Studio. I have had coworkers need clarification because they may not alias or they use server.owner.object for every naming in their statements which I find very hard to read when the joins start rolling in.
June 22, 2011 at 1:23 pm
As always, Gus has imparted a wealth of information. I get the trained habits. I guess I trained myself on how to eyeball code and it works for me. I've had some tell me it's very easy for them to read and others says it's kind of difficult to read.
Todd Fifield
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply