Accidentally giving a column an alias
-
We all know that if you want to give a column a different name in the output you can do it two ways...
First
SELECT 'MyColumnName' = Column_A FROM Table
OR
SELECT Column_A AS MyColumnName FROM Table
Well apparently there is a way that sql server will apply an alias when you would least expect it(in my opinion) as shown in this example.
-------
CREATE TABLE #Table (Column_A int, Column_B datetime, Column_C int)
INSERT INTO #Table (Column_A, Column_B, Column_C) SELECT 1, getdate(), 5
INSERT INTO #Table (Column_A, Column_B, Column_C) SELECT 1, getdate(), 5
INSERT INTO #Table (Column_A, Column_B, Column_C) SELECT 1, getdate(), 5
SELECT
Column_A -- no comma
Column_B,
Column_C
FROM #Table
DROP TABLE #Table
----------
As you can see from the output you only receive the data from Column_A and Column_C with Column_A having the alias name "Column_B". I don't like it! This really should be returned as an error or is this how the sql standard works?!?! Is this still how things work in SQL Server 2005?
-- JP
-
There are probably three ways.
Keyword "AS" in you second way is optional.
Should it be named "second way of second way"?
That's proper T-SQL syntax, so there is nothing to fix in further versions.
Sorry.
_____________
Code for TallyGenerator -
Having AS as optional seems a little loose to me.
-- JP
-
This is how it's worked since the dinosaurs...
It's an old 'trap' when unintended, the solution is to keep tounge in cheek and place your commas appropriate.
/Kenneth
-
The dinosaurs are dead so why can't this be killed with them
-- JP
-
Well, I've never used this particular style to alias myself, I just know that it's there and need to be 'looked out for'. Who knows? Perhaps it will go away in the future.
/Kenneth
-
Jonathon
I agree with Kenneth... proper syntax calls for the column name to be followed with either a comma, an AS with an alias followed by a comma, or an alias followed by a comma (except for final column name, of course). I'd rather see it require the AS, but knowing the comma is required should be enough. And, if you do leave one out, it's pretty easy to catch as the example code shows.
Of course, you could always use the "alias = columnname," format for everything... if you leave a comma out then, boom! But then, that would also make the myth of 100% portable code even more unlikely...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I prefer the 'ColumnAlias' = column_A approach because it very plain to see which column names are made up and which ones are actual columns that exist in the database.
Is this method of giving a column name an alias not portable to other databases?
-- JP
-
Correct... not portable in most cases... certainly not portable to Oracle. But I'm not one of those that will forsake the use of a good tool just for some chance at portability. It's kinda like not using the Square Root key on a calculator just because not all calculators have one... crazy thing to do...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply