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);