September 2, 2015 at 9:30 am
Steve Jones - SSC Editor (9/2/2015)
The sixth one should have had brackets. That's been corrected. However, I missed a few since there could be no joiner (=/AS) in there, so it's probably more like 12. The CTE thing is interesting. Not sure I think this is an alias, as it's really a column name specified for the position, not an alias, but it is debatable.The subquery one is also interested.
I'll award back points later. This likely isn't a great question.
A good question because it made for interesting discussion.
September 2, 2015 at 9:30 am
Steve Jones - SSC Editor (9/2/2015)
I do agree with the = and try to use it, precisely because complex code becomes hard to read when the name is buried far to the right of my screen. However my habit is "as", so I struggle to change and get some inconsistent code.
Assuming you put one and only one column on each row of text this shouldn't happen except for the occasional really complex string manipulation or complex calculation.
My main argument against using ColumnName = Column is that it looks like assignment and if you really do mean to be assigning a value to a variable and you happen to forget the @ it is really hard to find the problem. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 2, 2015 at 9:49 am
Sean Lange (9/2/2015)
Steve Jones - SSC Editor (9/2/2015)
I do agree with the = and try to use it, precisely because complex code becomes hard to read when the name is buried far to the right of my screen. However my habit is "as", so I struggle to change and get some inconsistent code.Assuming you put one and only one column on each row of text this shouldn't happen except for the occasional really complex string manipulation or complex calculation.
My main argument against using ColumnName = Column is that it looks like assignment and if you really do mean to be assigning a value to a variable and you happen to forget the @ it is really hard to find the problem. Just my 2¢.
It is an assignment, albeit a metadata one.
I'm not sure what you mean by the @? Are you saying if you write
select @i = mycol from mytable
and you forget the @ there? I think that's a simple find.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 2, 2015 at 9:58 am
Carlo Romagnano (9/2/2015)
Ed Wagner (9/2/2015)
Stewart "Arturius" Campbell (9/2/2015)
Methinks thisis going to turn into an interesting discussion.Thanks fot the question, Steve
I'm thinking that your thinking is right. 😉
I wonder if this counts as an aliasing twice or three times:
WITH cte(EmployeeID, Name) AS (SELECT ID UselessAlias, Name
FROM dbo.Employees
)
SELECT EmployeeID SomeID, Name
FROM cte;
Inside the CTE, the ID column is being aliased as UselessAlias.
Its being returned from the CTE as EmployeeID. Would you call this an alias?
In the outer SELECT, its then being aliased as SomeID.
So, would you call this 2 or 3 aliases?
The question ask about SELECT and not CTE.
Agreed. So what is in a CTE? It contains a SELECT statement. You can do multiple things with it, one of which is SELECT.
September 2, 2015 at 9:59 am
marcia.j.wilson (9/2/2015)
Steve Jones - SSC Editor (9/2/2015)
The sixth one should have had brackets. That's been corrected. However, I missed a few since there could be no joiner (=/AS) in there, so it's probably more like 12. The CTE thing is interesting. Not sure I think this is an alias, as it's really a column name specified for the position, not an alias, but it is debatable.The subquery one is also interested.
I'll award back points later. This likely isn't a great question.
A good question because it made for interesting discussion.
Yup - I couldn't agree more, Sean. I expected it to be a good one.
September 2, 2015 at 10:03 am
marcia.j.wilson (9/2/2015)
Steve Jones - SSC Editor (9/2/2015)
The sixth one should have had brackets. That's been corrected. However, I missed a few since there could be no joiner (=/AS) in there, so it's probably more like 12. The CTE thing is interesting. Not sure I think this is an alias, as it's really a column name specified for the position, not an alias, but it is debatable.The subquery one is also interested.
I'll award back points later. This likely isn't a great question.
A good question because it made for interesting discussion.
Just in case anyone's counting, I like using AS for alias. It's obvious what's intended and it's much less ambiguous than =.
September 2, 2015 at 10:36 am
Steve Jones - SSC Editor (9/2/2015)
Sean Lange (9/2/2015)
Steve Jones - SSC Editor (9/2/2015)
I do agree with the = and try to use it, precisely because complex code becomes hard to read when the name is buried far to the right of my screen. However my habit is "as", so I struggle to change and get some inconsistent code.Assuming you put one and only one column on each row of text this shouldn't happen except for the occasional really complex string manipulation or complex calculation.
My main argument against using ColumnName = Column is that it looks like assignment and if you really do mean to be assigning a value to a variable and you happen to forget the @ it is really hard to find the problem. Just my 2¢.
It is an assignment, albeit a metadata one.
I'm not sure what you mean by the @? Are you saying if you write
select @i = mycol from mytable
and you forget the @ there? I think that's a simple find.
That is what I meant. I ran into that situation once where it took a long time to figure out why a procedure somebody else had written was doing weird things occasionally it was because of this exact thing. They accidentally left off the @ and it was buried in a mountain of other code and got lost. This was many years ago in sql 2000 and I didn't know of any handy code formatters like we have today. I suspect this would be a lot easier to find today when it is easy to reformat code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 2, 2015 at 11:49 am
Ed Wagner (9/2/2015)
Agreed. So what is in a CTE? It contains a SELECT statement. You can do multiple things with it, one of which is SELECT.
I see a CTE as more like a view that's inline than a SELECT. Hence why I'm not sure I consider this an alias.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 2, 2015 at 11:59 am
Has anyone suggested this as a method?
SELECT
A, B, C
FROM
(
SELECT
1, 2, 3
) AS X (A, B, C)
September 2, 2015 at 12:32 pm
Very nice :cool:, but as I see most of us prefer (and me too) 3 definitions of aliases
Thanks
September 2, 2015 at 7:25 pm
Bad answer, maybe. Bad question too, maybe, because it's ambiguous as to whether wrapping an alias makes a different way of aliasing. Or maybe not bad at all, because it's a fun question. I see that people are dreaming up ways to make the answer more that 12 (which would have been my answer if it had been an option); Without 12 the only answer (if one excludes things like the CTE argument, which I think is amusing nonsense) is 3 (that is "ignore the wrappers"). But 6 does seem just plain wrong.
If I don't wrap the alias in anything, I can write
SELECT alias = x
SELECT x AS alias
SELECT x alias
and that's 3 ways, which is the answer if one ignores wrapping the alias up somehow.
However, wherever I have alias above I can wrap it in three different ways, and one unwrapped and three wrapped the three things makes 12 versions altogether, because 3 time 4 is 12 (not 6
SELECT alias = x
SELECT x AS alias
SELECT x alias
SELECT [alias] = x
SELECT x AS [alias]
SELECT x [alias]
SELECT 'alias' = x
SELECT x AS 'alias'
SELECT x 'alias'
SELECT "alias" = x
SELECT x AS "alias"
SELECT x "alias"
those are the 12 ways if one counts wrapping as making a difference.
I looked at the question and saw that 6 was the highest option. That said to me "evidently wrapping differences don't count, because all the numbers are less than 12, so it must be three".
Tom
September 2, 2015 at 7:53 pm
Steve Jones - SSC Editor (9/2/2015)
I do agree with the = and try to use it, precisely because complex code becomes hard to read when the name is buried far to the right of my screen. However my habit is "as", so I struggle to change and get some inconsistent code.
I can sympathize with that. I worked with Oracle very early on and I've worked with Ingres and played with Postgres (and had a very brief connection with DB2, which was sheer hell after using decent or half-way decent RDBMS; can't say I worked with it, because it didn't work) so I grew into the habit of either not aliasing or using (or omitting) AS. I actually think the "=" method is far better, but changing existing code to use it was just too error-prone. I keep on forgetting to use it with new code.
Tom
September 3, 2015 at 3:06 am
Steve Jones - SSC Editor (9/2/2015)
Ed Wagner (9/2/2015)
Agreed. So what is in a CTE? It contains a SELECT statement. You can do multiple things with it, one of which is SELECT.I see a CTE as more like a view that's inline than a SELECT. Hence why I'm not sure I consider this an alias.
A CTE is a Common Table Expression, which you can use in statements, in place of another row source, but not on it's own.
I don't see how it is in any way like a view, which is a separate object, defined in the database as a unique entity, while a CTE is just an expression, which occurs in the definition of other objects or ad-hoc queries.
As to the question of ways to alias a column, I would argue there are just three methods, each with optional elements.
Two methods in the SELECT clause:
<select_list> ::={
*
| { table_name | view_name | table_alias }.*
| {
[ { table_name | view_name | table_alias }. ]
{ column_name | $IDENTITY | $ROWGUID }
| udt_column_name [ { . | :: } { { property_name | field_name }
| method_name ( argument [ ,...n] ) } ]
| expression
[highlight="#ffff11"][ [ AS ] column_alias ][/highlight]
}
| [highlight="#ffff11"]column_alias = expression[/highlight]
} [ ,...n ]
1. [ AS ] column_alias
2. column_alias = expression
...And one method in the "table source" of a query (an example from the FROM clause, but they are all the same)
[ FROM { <table_source> } [ ,...n ] ]<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
[ <tablesample_clause> ]
[ WITH ( < table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause>
| derived_table [ [ AS ] table_alias ] [highlight="#ffff11"][ ( column_alias [ ,...n ] ) ][/highlight]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
| @variable [ [ AS ] table_alias ]
| @variable.function_call ( expression [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
| FOR SYSTEM_TIME <system_time>
}
3. Positional aliasing ( column_alias [ ,...n ] )
Anything else is just decoration on these three.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 3, 2015 at 6:23 am
Todd Reddinger (9/2/2015)
handkot (9/1/2015)
I think the same thing
AS AliasAndAS 'Alias'also i can write
AS [Alias], or write CTEso there are only three ways: "=", "AS" and column
+1
I agree with this. x, [x], 'x' (deprecated?), and "x" are not different ways to specify an alias. They're different ways to name a column.
September 3, 2015 at 9:08 am
Sean Lange (9/2/2015)
I am guessing that Aaron did NOT convince Steve to use = instead of as. 😉I have to say that I disagree with Aaron on this. I find it obtuse to say alias = column. It looks like setting a value to a variable to me.
+1
Don Simpson
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply