June 17, 2011 at 6:41 am
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.
June 17, 2011 at 6:51 am
dont thin it makes a difference. one thing i have noticed is if i create an alias like this:
select
'alias' = firstname + ' ' Surname
from
people
i can't reference that alias in the where clause.
June 17, 2011 at 6:57 am
I'm accustomed to using "AS" for aliasing, so it's what I use. Can't say it's got a real advantage.
Since table aliases have to be "following style" (using "AS" or an implicit "AS"), using that style on columns does keep it consistent. On the other hand, CTEs go name-first, so there goes consistency!
- 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 17, 2011 at 7:02 am
I've (almost) always used 'AS' as well. I was handed this query to work on though and the first thing I need to do is clean it up so I can tell from where fields are coming. I was suddenly struck that it'd be a whole lot easier to read the result field names if they were all lined up pretty.
June 17, 2011 at 7:27 am
I tend to use tabs to line things up in cases where I've got a lot of field names that are all aliased. If the vast majority of fields are aliased, I might even alias a field name to itself to keep my columns correct.
SELECTSomeField1 [FieldA],
SomeField2 + SomeField3 [FieldB],
FieldC [FieldC],
(LongCalculation that doesnt really fit in one line AND has TO be +
aliased ON the second line INSTEAD) [FieldD],
etc.
June 17, 2011 at 7:44 am
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.
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 17, 2011 at 8:11 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.
Around the time that SQL 2000 was released, I remember reading that the "AS" was going to be required in some future version. I don't know if that is still the plan, but I always include the "AS" so that I'm in the habit if this ever is implemented.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 17, 2011 at 8:55 am
drew.allen (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.Around the time that SQL 2000 was released, I remember reading that the "AS" was going to be required in some future version. I don't know if that is still the plan, but I always include the "AS" so that I'm in the habit if this ever is implemented.
Drew
Can you find anything to back that up? I tried a few searches with no success...
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 17, 2011 at 9:26 am
But is there a practical difference between
(field1 + field2) [alias1]
and
[alias1] = (field1 + field2)
?
June 17, 2011 at 10:03 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.
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.
June 17, 2011 at 10:12 am
Back on topic.
I believe the (field1 + field2) [AS] Alias is the ANSI standard. So if you're writing code to 'standards' then put the alias after the column(s).
It may also make a difference depending on your database and database settings on how the line is interpreted.
June 17, 2011 at 10:22 am
davidandrews13 (6/17/2011)
dont thin it makes a difference. one thing i have noticed is if i create an alias like this:
select'alias' = firstname + ' ' Surname
from
people
i can't reference that alias in the where clause.
For what it's worth - this particular practice is on the deprecation list (i.e. will not be supported in future releases) http://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx
While the = itself is okay to create aliases, assigning to a string rather than a valid column alias is not.
so
select
[alias] = firstname + ' ' Surname
from
people
is the recommended change
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2011 at 10:38 am
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.
I did use the word 'consider', as I realise that this varies from one scenario to another.
Personally, I find it easier to read code without loads of AS's - I like things concise and without clutter. And it helps prevent carpal tunnel syndrome, as I write a lot of code.
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 17, 2011 at 12:51 pm
--Using ColumnName in WHERE clause returns expected results
[alias] = ColumnName
FROM dbo.TableName
WHERE ColumnName > val
-- Using an alias in Where clauses returns empty rst - NOT expected
SELECT
[alias] = ColumnName
FROM dbo.TableName
WHERE [alias] > value
-- Alias works fine in Order By clause - returns expected resultset
SELECT
[alias] = ColumnName
FROM dbo.TableName
WHERE ColumnName > value
ORDER BY [alias] DESC
Ugggh! Tried to do a little edit & accidentally wiped out my post. When executing a query, sql server will go thru a very specific ordered process starting with the FROM clause and then filtering the results with the WHERE clause. Because the SELECT clause hasn't come into play yet, and won't until much later, the alias is not available yet because it hasn't been created. But because the SELECT clause is processed before the ORDER BY clause, it can be used by the ORDER BY. Check out the first chapter of Itzik BenGan's outstanding book "T-SQL querying" for a full breakdown.
HTH
June 17, 2011 at 1:02 pm
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, col2
but missed a comma, resulting in col1 aliased as col2, and missing col2 completely.
Ex. -
Select col1 col2
which is equivalent to
Select col1 AS col2
... but not quite as clear.
HTH:-)
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply