none
Column Alias Usage Everywhere in a Query Following Definition

All replies

  • My wish: column alias usage everywhere in the query, not only in the ORDER BY clause.

    In the following example you get a syntax error due to column alias usage in the WHERE clause:

    USE AdventureWorks2008;
    
    SELECT PC.Name	AS Category, 
      PSC.Name AS Subcategory,
      PM.Name	AS Model, 
      P.Name	AS Product
    FROM Production.Product AS P
     FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID
     FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
     JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
    WHERE Category = 'Bike'
    ORDER BY Category, Subcategory, Product ;
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, October 01, 2010 8:19 PM
    Moderator
  • My wish: column alias usage everywhere in the query, not only in the ORDER BY clause.

    That would violate the logical query flow as defined in the ANSI SQL standard (FROM comes first, then WHERE, GROUP BY, HAVING and not until now comes the SELECT list, then ORDER BY and TOP). I'm not sure MS would want to divert from the SQL standard, but a connect entry would at least open up this for discussion and voting.
    Tibor Karaszi, SQL Server MVP | web | blog
    Sunday, October 03, 2010 8:23 AM
    Moderator
  • Tibor, to be absolutely frank, this logic is not 100% forced in the all cases -

    select a.f1, a.f2
    from a left outer join b.. 
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, October 04, 2010 1:22 AM
  • Dmitri,

    I'm sure there are seveal imperfections in both ANSI SQL as well as T-SQL in this regard. In fact, I blogged one of those just yesterday: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/03/why-can-t-we-have-column-alias-in-order-by.aspx.

    I fail to see how your example show such diversion, though. It seems to be a straight-forward outer join. Perhaps you can elaborate, fill in what I am missing?


    Tibor Karaszi, SQL Server MVP | web | blog
    Monday, October 04, 2010 8:21 AM
    Moderator
  • That would violate the logical query flow as defined in the ANSI SQL standard (FROM comes first, then WHERE, GROUP BY, HAVING and not until now comes the SELECT list, then ORDER BY and TOP).


    That may be illogical though because you always start the query with SELECT.

    It is simply inconsistent that you can only use the column alias in ORDER BY. It would improve readability and increase programmer's productivity if it can be used in all parts of the query when appropriate. 

    VOTE for this feature at Connect:

    https://connect.microsoft.com/SQLServer/feedback/details/610120/column-alias-usage-everywhere-in-a-query


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, October 04, 2010 1:26 PM
    Moderator
  • Dmitri,

    I'm sure there are seveal imperfections in both ANSI SQL as well as T-SQL in this regard. In fact, I blogged one of those just yesterday: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/03/why-can-t-we-have-column-alias-in-order-by.aspx .

    I fail to see how your example show such diversion, though. It seems to be a straight-forward outer join. Perhaps you can elaborate, fill in what I am missing?


    Tibor Karaszi, SQL Server MVP | web | blog

    Oh, Tibor, sorry - I was under impression that you are talking about EXCLUDE suggestion, not about column aliases. Your point as absolutely valid there.

    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, October 04, 2010 1:47 PM
  • This is an issue that comes up at least somewhat frequently in the forums.  I think this feature is available in Oracle.  I frequently use either a derived table or a cross apply to simulate this.  I will check to verify if this is available in Oracle.

    I know you won't like my political answer, Kalman, but this is not a priority for me.  I like the concept of this feature but there are a few features that I prefer over this -- chiefly Itzik's issue of expanded analytic function capabilities and also the ability to have inline scalar functions.

    EDIT:

    When I tried to do something like this in Oracle it failed.

    Monday, October 04, 2010 2:01 PM
    Moderator
  • Now an question which is related to this is going in sql server quiz 2010 in beyondrelational.com

     

    Suppose, IF you wants to order by Year and Month, how would you like to write this query??? In that case, we need to put the express Year(OrderDate) and Month(OrderDate) in Order by Clause. So, Engine needs to evaluate twice.

    Assume its a complex expression, we wants to order by that expression. Then we need to write at two places. There is chance that Engine might evaluate twice.. (Of course, cross apply is there. But its different)

    Even Optimizer becomes intellegent and compute it once, what about some expression like

    select Year(ColName) from TableName order by Year(ColName) + 5

    If some one write like above, it will re-evaluate it again................ 

     

    I would like to see the existing functionality to be as it is same, one more i would like to see is, using the aliases in where clause also........

     

     

     

     

    • Edited by ramireddy Tuesday, October 05, 2010 3:47 AM
    Monday, October 04, 2010 2:03 PM
  • "SELECT * EXCLUDE [column] FROM [table]"

    I would not like to see it as part of the syntax, as you should explicitly select the columns that are going to be used.

    However perhaps a change to SSMS so that when you drag the column list with "shift" a column select dialog box pops up, where you can deselect/select as needed.

    If I have a big warehouse table, then i tend to use this rather than type out column lists:

    SELECT
    STUFF((
    SELECT ','+LEFT([TABLE_NAME],3)+ '.'+QUOTENAME([COLUMN_NAME])
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE [TABLE_SCHEMA] = 'dbo'
    AND [TABLE_NAME] = 'spt_values'
    --AND [COLUMN_NAME] NOT IN ('')
    FOR XML PATH('')), 1, 1, ' ') ColumnList
    

     


    Jon
    Monday, October 04, 2010 2:05 PM
  • These two expressions did not evalue in Oracle:

    select 
     1 as b,
    from dual
    where b=1;
    
    -- S0022(904)[Oracle][ODBC][Ora]ORA-00904: "B": invalid identifier
    -- (0.00 secs)
    
    select 
     1 as b,
     b+1
    from dual;
    
    -- S0022(904)[Oracle][ODBC][Ora]ORA-00904: "B": invalid identifier
    -- (0.00 secs)
    

    select 1 as b
    from dual
    order by b;
    -- ------- Output: --------
    --  B
    --  ----
    --  1

     

    My concern with this is that doing this kind of aliasing might require that the compiler need to make another pass through the syntax and I am probably willing to accept that. 

     

    Monday, October 04, 2010 2:15 PM
    Moderator
  • kent, 

    Try "b" in order by clause.....

     

    I think Oracle also pretty much same as SQL. Alias Column Name can't be referenced in where clause or group by clause. But we can reference in Order By Clause............

    Monday, October 04, 2010 2:17 PM
  • That works in Oracle:

    select 1 as b
    from dual
    order by b;
    
    -- ------- Output: --------
    -- B
    -- ----
    -- 1
    

     

    Monday, October 04, 2010 2:45 PM
    Moderator
  • I know you won't like my political answer, Kalman, but this is not a priority for me.  I like the concept of this feature but there are a few features that I prefer over this -- chiefly Itzik's issue of expanded analytic function capabilities and also the ability to have inline scalar functions.

    Kent,

    The everyday productivity of 1 million (ballpark) SQL developers/DBAs is negatively impacted by the peculiar column alias usage rules.

    Assuming that 1 minute cost $1 for the employer (2010) and 4 minutes lost on the average per day, that is $4 million loss daily, $1 billion yearly loss to the world economy.

    Wonder if someone can do similar estimate for other new feature requests?

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, October 04, 2010 3:33 PM
    Moderator
  • I know you won't like my political answer, Kalman, but this is not a priority for me.  I like the concept of this feature but there are a few features that I prefer over this -- chiefly Itzik's issue of expanded analytic function capabilities and also the ability to have inline scalar functions.

    Kent,

    The everyday productivity of 1 million (ballpark) SQL developers/DBAs is negatively impacted by the peculiar column alias usage rules.

    Assuming that 1 minute cost $1 for the employer (2010) and 4 minutes lost on the average per day, that is $4 million loss daily, $1 billion yearly loss to the world economy.

    Wonder if someone can do similar estimate for other new feature requests?

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    Well said and you might be right.  I will gnaw on this a little bit.  As always, thank you for picking me up, Kalman.

    :)

     

    Monday, October 04, 2010 3:46 PM
    Moderator
  •  

    Assuming that 1 minute cost $1 for the employer (2010) and 4 minutes lost on the average per day, that is $4 million loss daily, $1 billion yearly loss to the world economy.

     

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wow Kalman, you live in a perfect world where every minute counts.
    Monday, October 04, 2010 4:05 PM
  • Another potential problem has to do with SELECT statements that have the same output column names.  For instance:

    select
     1 as A,
     2 as A
    /* -------- Output: --------
    A   A
    ----------- -----------
    1   2
    
    (1 row(s) affected)
    */
    
    select
     1 as A,
     2 as A
    order by a
    /* -------- Output: --------
    Msg 209, Level 16, State 1, Line 12
    Ambiguous column name 'a'.
    */
    
    

    The first statement is presenty legal SQL syntax.  I can certainly understand any recommendation against using that kind of syntax, but nonetheless, the syntax is legal.  A barier to doing what you suggest to me would imply that the currently legal syntax would need to be deprecated.  This means that time would be needed to allow anyone that has code like this to change such code.  Is such a deprecation already published?

    EDIT:

    I posted this issue at the link you gave.  Rob Volk posted a CTE workaround for this issue.  I posted a CROSS APPLY workaround based on syntax similar to Rob's CTE workaround.

    Monday, October 04, 2010 5:15 PM
    Moderator
  • VOTE for this feature at Connect:
    https://connect.microsoft.com/SQLServer/feedback/details/610120/column-alias-usage-everywhere-in-a-query

    Voted. Down. You cannot use something until it is defined, and the SELECT list is defined at the end. Consider this:

    CREATE TABLE one (a int NOT NULL)
    CREATE TABLE two (c int NOT NULL,
                      d int NOT NULL)
    
    SELECT a AS c
    FROM   one
    CROSS  JOIN two
    WHERE  c = 1

    This means something today. With your suggestion, it would mean something else or be ambiguous. Whatever, it would break backwards compatibility.

    If you want to name an expression, and reuse the name you can use a CTE.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Monday, October 04, 2010 8:28 PM
  • Erland:

    Did you perhaps respond to the wrong post?

    Monday, October 04, 2010 8:32 PM
    Moderator
  • He responded to Kalman's suggestion. I didn't vote, but I agree that it will break existing functionality, so unlikely to be implemented.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 04, 2010 8:42 PM
    Moderator
  • and the SELECT list is defined at the end. Consider this:

    Why would you use column alias which is the same as another column name?

    I always start the query with SELECT ....

    In fact you should be able to use a column alias later in the SELECT list itself. This is basic Comp. Science 101. There is no space shuttle wizardry to it...or rocket to the Mars science either...

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, October 04, 2010 9:37 PM
    Moderator
  • Why would you use column alias which is the same as another column name?

    Well, why not? There may be all sorts of reasons. If you are writing a join against two wide tables, you may not even be aware of the name clash. In fact, there may be even be a name clash at the time you write the query. Then, two years, later, someone adds a new column to one of the tables...

    I always start the query with SELECT ....

    I guess most people do. However, the logical processing order is really FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY.

    In fact you should be able to use a column alias later in the SELECT list itself. This is basic Comp. Science 101. There is no space shuttle wizardry to it...or rocket to the Mars science either...


    But it is SQL where everything happens at once. As I said, it break backwards compatibility, and it also introduces nasty issues if an alias has the same name as a column.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Monday, October 04, 2010 10:08 PM
  • I am quite confident that MS software wizards can figure a way to implement it and phase it into production.

    Do you find it odd that table alias can be used anywhere in a query, even prior to definition? Column alias on the other hand quite restricted?

    SELECT p.Color, AvgPrice=AVG(p.ListPrice)
    FROM AdventureWorks2008.Production.Product p
    WHERE p.ListPrice > 0
    GROUP BY p.Color
    ORDER BY p.Color
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, October 04, 2010 11:23 PM
    Moderator
  • New thread for the column alias issue:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/4ad14cad-1e61-43a0-96f8-70a506106a00


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 12:12 AM
    Moderator
  • <<That may be illogical though because you always start the query with SELECT.>>

    Noevertheless, that is how the language is defined, and has been since the eghties. Are you not concerned with backward compatibility for the probably billions of SQL code "out there"? Erland stated a very simple example showing how your proposal can cause problems. Something like:

    SELECT a, b AS c
    FROM tbl
    WHERE c = 23

    The table above has a column named "c".
    The problem isn't that it might be unwise to write a query that way. The problem is that your proposal changes the meaning of SQL. Basically, we get a new language. I could potentially appreciate your proposal seen from a consutlant perspective, since I could get endless hours wading through SQL code for clients verifying that thir code still mean the same thing with the new syntax - but I doubt it will do good for the product SQL Server.

    Also, how do you propose to handle the diversion from the ANSI SQL standard. Lobby for changing the standard? Or ignoring the standard?


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 5:00 AM
    Moderator
  • I also replied in the other thread, but reply here as well, for completeness:

    The language is defined as having the ligical evaluation order as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, TOP, and has been since the eghties. TOP is, of course, MS propriatary.

    So, you are proposing changing the language to something else, since the fundaments of SQL (as per above) no longer applies.

    Are you not concerned with backward compatibility for the probably billions of SQL code "out there"? Erland stated a very simple example showing how your proposal can cause problems. Something like:

    SELECT a, b AS c
    FROM tbl
    WHERE c = 23

    The table above has a column named "c".
    The problem isn't that it might be unwise to write a query that way. The problem is that your proposal changes the meaning of SQL. Basically, we get a new language. I could potentially appreciate your proposal seen from a consutlant perspective, since I could get endless hours wading through SQL code for clients verifying that thir code still mean the same thing with the new syntax - but I doubt it will do good for the product SQL Server.

    Also, how do you propose to handle the diversion from the ANSI SQL standard. Lobby for changing the standard? Or ignoring the standard?


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 5:04 AM
    Moderator
  • My wish: column alias usage everywhere in the query, not only in the ORDER BY clause.

    In the following example you get a syntax error due to column alias usage in the WHERE clause:

    USE AdventureWorks2008;
    
    
    
    SELECT PC.Name	AS Category, 
    
     PSC.Name AS Subcategory,
    
     PM.Name	AS Model, 
    
     P.Name	AS Product
    
    FROM Production.Product AS P
    
     FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID
    
     FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
    
     JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
    
    WHERE Category = 'Bike'
    
    ORDER BY Category, Subcategory, Product ;
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    I also wish the same , and as i have worked heavily on teradata for last two years , and really liked that teradata supports this. And there are many other things thats teradata additionally supports one of the example is below

     

    selelct * from table1 where

    (field1,field2) in (select field1,field2 from table2)

    Tuesday, October 05, 2010 5:07 AM
  • Tibor,

    The *=, =* was phased out, the JOIN syntax was introduced (instead of table list & WHERE) and the world did not come to an end.

    Why can use table aliases everywhere in a query? But not column aliases? Why are you allowed to use them in ORDER BY at all? Why don't you just banish column alias altogether?

    I don't know why the ANSI SQL folks did it, but certainly was a big mistake.

    My proposal is a major productivity gain for SQL developers. It is also logical. After all, Computer Science is about logic.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 5:17 AM
    Moderator
  • I think I replied to below already but let me do it one more time:

    <Why can use table aliases everywhere in a query? >
    Because the FROM clause is evaluated first in the query.

    <But not column aliases? >
    Because the SELECT list is evaluate almost last.

    <Why are you allowed to use them in ORDER BY at all? >
    Because ORDER BY is evaluated after the SELECT list.

    <Why don't you just banish column alias altogether?>
    Because column aliases are useful. They allow you to ORDER BY come derived column and they also allow you to rename a column in the result set.

    Now, please answer my questions about backwards compatibility and how to handling the diversion from the ANSI SQL standard.


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 5:27 AM
    Moderator
  • >Because the FROM clause is evaluated first in the query

    But the parser starts with SELECT and can resolve the column aliases prior to evaluation.

    Colum aliases would be even more useful if you can use them everywhere not only in ORDER BY.

    I don't think backward compatibility would be a major issue in the hands of capable MS software engineers.

    T-SQL has other features which are not ANSI SQL standard.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 5:36 AM
    Moderator
  • <But the parser starts with SELECT and can resolve the column aliases prior to evaluation.>

    That is how the product implements the language. I'm not talking about that. I'm talking about *the meaning* of the language. This is what you are proposing to change.

    <I don't think backward compatibility would be a major issue in the hands of capable MS software engineers.>

    This has nothing to do with "capable MS software engineers". If you change the meaning of the language, you need to consider the existing code and what impact that has. See mine and Erland earlier example of such a query.

    So, basically you propose to ignore the backward compatibility issues and also ignore diversion from the ANSI SQL language. To that I voted "no".

    Please be aware that you are not just adding a feature. You are changing the meaning of the language, this is important. Again, the logical avaluation order for a query is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. You are proposing a change of this, whcih is changing the meaning and semantics of the language.


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 5:47 AM
    Moderator
  • Please be aware that you are not just adding a feature. You are changing the meaning of the language, this is important. Again, the logical avaluation order for a query is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. You are proposing a change of this, whcih is changing the meaning and semantics of the language.


     

    I don't think so Tibor. I am proposing a trivial, but very useful, improvement to the syntax.

    -- Proposed column alias changes
    SELECT	[Year]=YEAR(OrderDate),
    		[Month]=MONTH(OrderDate), 
    		TotalPurchase=SUM(TotalDue)
    FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
    WHERE [Year] > 2002
    GROUP BY [Year],[Month]
    ORDER BY [Year],[Month]


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    Tuesday, October 05, 2010 6:03 AM
    Moderator
  • The query parser must start with SELECT, cannot possibly start with FROM. Therefore it can resolve the column aliases in a satisfactory manner for database engine evaluation.

    -- Proposed column alias changes
    SELECT	[Year]=YEAR(OrderDate),
    		[Month]=MONTH(OrderDate), 
    		TotalSales=SUM(TotalDue)
    FROM AdventureWorks2008.Sales.SalesOrderHeader
    WHERE [Year] > 2002
    GROUP BY [Year],[Month]
    ORDER BY [Year],[Month]


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    Tuesday, October 05, 2010 6:08 AM
    Moderator
  • The language is defined as evaluation order FROM, WHERE, GROUP BY, HAVING, SELECT and ORDER BY. You are proposing that we can refer to a column alias, something which happens close to last in the evaluation order. How can you claim that this doesn't change the sematics of the language?

    Both me and Erland posted examples of queries that becomes very problematic to handle with your proposal (introduce an alias with same name as an existing column in the table). Please state how to handle all existing SQL code. Note that all SQL code using column alias can potentially run into change of meaning, this means we need to go through all existing SQL code using column alias in order to make sure the query has the same meaning as before. I just can't see that happening.

    I won't post anymore in this thread (unless something new is introduced), as it seems I just repeat myself over and over again. I think I have made my case, and all are welcome to vote on the connect item.


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 6:24 AM
    Moderator
  • You fail to differentiate between how the product (SQL Server) is implemeted and what a SQL query *means*. It is all about abstraction.

    I can't care less of MS changes how the product implements query execution.

    I do care if the meaning of my SQL queries change.

    I don't care how the product implements addition of the number 1 + 1. All I care about is that the result should be 2. I woudn't be happy if the result suddenly come out as 3. This is what I mean by changiung the meaning of the query, the semantics of SQL.


    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 6:29 AM
    Moderator
  • I agree with you Tibor. I fail to understand why "the world coming to an end" when column aliases are used everywhere in the query. What so special about ORDER BY? Why does it deserve column alias use? Why WHERE and GROUP BY became "orphans" in this regard?

    WHERE [Year] > 2002
    GROUP BY [Year],[Month]
    ORDER BY [Year],[Month]
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 6:44 AM
    Moderator
  • Because the SELECT list isn't evaluated until just before ORDER BY. Change that and you change the sematics of the language. It is as simple as that.
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, October 05, 2010 6:47 AM
    Moderator
  • Kalman

    Would  you expect to refer alias in the below query

    SELECT <columns>, ROW_NUMBER() OVER (ORDER BY col) rn FROM tbl

    WHERE rn>20


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 05, 2010 6:47 AM
    Answerer
  • Kalman, I think all the posts are explaining..........

    The Order of the SQL statements execution is

    1) First it will evalulate "From" statement

    2) "Where"

    3)"Group By"

    4) Having

    5) Select

    6) Order

    You are giving alias name in "select" clause, so, the alias Name is available to Order by clause. So, It used it.

    But while executing "Group By" or "having" or "where", alias name is not available to those clauses. So,  we can't use that alias names in those clauses.

    Its really not possible to change the Order of Evaluation by bringing "SELECT" into top.

    Suppose, If you evalulate "Select" before where ,group by   , its a lot of wastage of resources. Suppose see the example.

    select top 1 Cost + 10 from TableName where Cost = 100 order by ID
    

    Assume table consists of 10 million records, and the Cost = 100 is fetching only 10 records

    Suppose, if i evalulate "Cost + 10" before the evalulation of "where" clause, i am unnecessarily calculating "Cost + 10" value of 10 million records. Really there is no meaning to be having indexes also........

    If i evaluate "Cost + 10" after where clause and group by and having, that means, I am doing only necessary calculations.

    "SELECT" should be after "WHere,Group By,Having"

    Tuesday, October 05, 2010 6:52 AM
  • Hi SQLUSA,

    In order to understand why you are wrong in thinking the SELECT is the start of the query you need to understand relational theory and then ANSI SQL which is built around relational theory.

    The FROM clause (the set from which the restriction (the WHERE) and projection (the SELECT) are taken) happens first, without the FROM which forms a single set from which we then restrict and project there cannot be a SELECT not WHERE!

    Column aliases are formed after both the FROM and the WHERE, projection is the last relational operation to be performed, after that we then have the non-relational ORDER BY.

    IF you are really that bothered, and frankly this has never occurred to me in the 20 years I've been writing SQL then use a derived table to provide the final "set" that you can then use column aliases from.

    Eg.,

    SELECT colalias1, colalias2
    FROM (
      SELECT fullname AS colalias1, sid_amount AS colalias2
      FROM tbl
      ) AS d ( colalias1, colalias2 )
    WHERE colalias1 = 'sid'

    Hope that helps.

    Tony.

    Tuesday, October 05, 2010 6:53 AM
  • I know you won't like my political answer, Kalman, but this is not a priority for me.  I like the concept of this feature but there are a few features that I prefer over this -- chiefly Itzik's issue of expanded analytic function capabilities and also the ability to have inline scalar functions.

    Kent,

    The everyday productivity of 1 million (ballpark) SQL developers/DBAs is negatively impacted by the peculiar column alias usage rules .

    Assuming that 1 minute cost $1 for the employer (2010) and 4 minutes lost on the average per day, that is $4 million loss daily, $1 billion yearly loss to the world economy.

    Wonder if someone can do similar estimate for other new feature requests?

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    Hi Kalman,

    I would be much more interesteed in an estimate of the loss when this feature would be implemented.

    As others have already explained, this feature would breakk backward compatibility. It is currently allowed to "swap" column names in a SELECT (e.g. SELECT Col1 AS Col2, Col2 AS Col1) - your proposal would radically affect the outcome of such queries. And while a query as idiotic as this one will not be in my database, there might be other, more subtle situations where something like this will happen. I'd have to check and double check all my code before I could upgrade to the version that implements your proposal.And plan for extended testing. And probably still be faced with undetected problems after the upgrade.

    Even with 4 minutes per day (which is probably way more than it would really save me), it would take years to earn back this major investment.  That alone suffices to vote down.

    Another reason is compatibility. Though many people claim that SQL standardization is dead, I still support all efforts toward ANSI compatibility, and I still criticise every move that violates the standard. This would defintely violate the standard. Another reason to vote down. (Unfortunately, connect gives me only one vote, regardless of the number of arguments).


    -- Hugo Kornelis, SQL Server MVP
    Tuesday, October 05, 2010 7:15 AM
  • > Do you find it odd that table alias can be used anywhere in a query, even prior to definition? Column alias on the other hand quite restricted?

    Not the least, because it doesn't. This is illegal:

    SELECT ...
    FROM   tbl1 a
    JOIN   tbl2 b ON b.col = c.col
    JOIN   tbl3 c ON a.col = c.col

    Sure, you can use all the aliases in the SELECT list, but the SELECT list comes after the from clause logically.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, October 05, 2010 7:17 AM
  • Another potential problem has to do with SELECT statements that have the same output column names.  For instance:

    select
    
     1 as
     A,
     2 as
     A
    /* -------- Output: --------
    A  A
    ----------- -----------
    1  2
    
    (1 row(s) affected)
    */
    
    
    select
    
     1 as
     A,
     2 as
     A
    order
     by
     a
    /* -------- Output: --------
    Msg 209, Level 16, State 1, Line 12
    Ambiguous column name 'a'.
    */
    
    
    

    The first statement is presenty legal SQL syntax.  I can certainly understand any recommendation against using that kind of syntax, but nonetheless, the syntax is legal.  A barier to doing what you suggest to me would imply that the currently legal syntax would need to be deprecated.  This means that time would be needed to allow anyone that has code like this to change such code.  Is such a deprecation already published?

    EDIT:

    I posted this issue at the link you gave.  Rob Volk posted a CTE workaround for this issue.  I posted a CROSS APPLY workaround based on syntax similar to Rob's CTE workaround.


    Hi Kent,

    There are much more common situations where multiple columns get the same column name in the SELECT list. Many queries that includes a self-join return result sets with duplicate column names. As do many queries that join on a common column and include SELECT * (I know, bad practice).

     


    -- Hugo Kornelis, SQL Server MVP
    Tuesday, October 05, 2010 7:25 AM
  • I don't want to rehash too much what has already been said. :)

    -Why do you want to do this?
    -You want to make the query easier to write and more easily readable.

    The reasons behind the request can easily be seen to be beneficial.

    However the ability to implement it is a problem. (See other posts in thread.)

    I would personally not like to see Microsoft change the way SQL is interpreted to provide this feature.

    So my vote would be No.

    SELECT substring(name,1,1) name
    FROM sysobjects
    WHERE name = 's'
    
    

     ...However I would vote yes for a change with Intellisence. So that when you write something like

    SELECT id Z 
    FROM sysobjects 
    WHERE Z.
    

    When I type the Z. after the where clause it should show me an option to ResolveAlias. Which would resolve the alias so that the query would be:

    SELECT id+1 Z 
    FROM sysobjects 
    WHERE (id+1)  < 10
    


     


    Jon
    Tuesday, October 05, 2010 8:23 AM
  • Tony,

    Why do I care how the database engine processes a query? Consider it a blackbox with regard to the proposed change.

    You are not trying to say that columns alias usage everywhere in a query cannot be implemented n number of ways by MS software engineers?

    I am talking about human productivity. Let's not worry about what the electronic machine does which is million times faster than our feeble biological brain.

    I start the query:

    SELECT Price = ListPrice,

    At this juncture, I should be able to use the column alias immediately:

    SELECT Price = ListPrice, SquareOfPrice = Price * Price,

    This is pretty standard in other computer languages, once you alias an expression, you can use the alias.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 11:02 AM
    Moderator
  • Jon,

    Nice workaround with IntelliSense, but not everybody uses it.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 11:05 AM
    Moderator
  • Sure, you can use all the aliases in the SELECT list, but the SELECT list comes after the from clause logically.

    Surely Erland.  But only the superexperts know that.  The average SQL developer thinks a query starts with  SELECT...

    The proposed change is improve the productivity of SQL developers without required knowledge about database engine internals.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 11:14 AM
    Moderator
  •  

    PLS. continue with column alias topic at the following link:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/4ad14cad-1e61-43a0-96f8-70a506106a00


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 12:06 PM
    Moderator
  • Another potential problem has to do with SELECT statements that have the same output column names.  For instance:

    select
    
    
    
     1 as
    
     A,
    
     2 as
    
     A
    
    /* -------- Output: --------
    
    A A
    
    ----------- -----------
    
    1 2
    
    
    
    (1 row(s) affected)
    
    */
    
    
    
    
    
    select
    
    
    
     1 as
    
     A,
    
     2 as
    
     A
    
    order
    
     by
    
     a
    
    /* -------- Output: --------
    
    Msg 209, Level 16, State 1, Line 12
    
    Ambiguous column name 'a'.
    
    */
    
    
    
    
    
    

    The first statement is presenty legal SQL syntax.  I can certainly understand any recommendation against using that kind of syntax, but nonetheless, the syntax is legal.  A barier to doing what you suggest to me would imply that the currently legal syntax would need to be deprecated.  This means that time would be needed to allow anyone that has code like this to change such code.  Is such a deprecation already published?

    EDIT:

    I posted this issue at the link you gave.  Rob Volk posted a CTE workaround for this issue.  I posted a CROSS APPLY workaround based on syntax similar to Rob's CTE workaround.


    Hi Kent,

    There are much more common situations where multiple columns get the same column name in the SELECT list. Many queries that includes a self-join return result sets with duplicate column names. As do many queries that join on a common column and include SELECT * (I know, bad practice).

     


    -- Hugo Kornelis, SQL Server MVP


    I agree and I am glad that Erland and you pointed this out.  As always, I appreciate you guys picking me up.

    As I said earlier.  When I start considering this issue what I see are barriers.

    I also voted this down.
    Tuesday, October 05, 2010 12:17 PM
    Moderator
  • Hi Hugo,

    Can you give a practical AdventureWorks query example which demonstrates the backward compatibility issue?

    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 12:22 PM
    Moderator
  • Kalman, I think all the posts are explaining..........

    The Order of the SQL statements execution is

    1) First it will evalulate "From" statement

    2) "Where"

    3)"Group By"

    4) Having

    5) Select

    6) Order

    Ramireddy,

    Obviously parsing starts with SELECT... I am not proposing to change the database engine execution logic. One way to implement is in a preprocessing phase.

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 12:26 PM
    Moderator
  • The folks losing time to this ANSI SQL standards compliant feature are those who don't know that the ORDER BY clause is logically evaluated after the SELECT clause. As for your request for an estimate on the analytic functions, take yours and multiply by 10 to cover lost time of end users who are waiting for cursor-based solutions to complete. That doesn't include the cost of debugging and fixing blocking/locking and other performance problems with cursor-based solutions.
    Tuesday, October 05, 2010 12:29 PM
  • The folks losing time to this ANSI SQL standards compliant feature are those who don't know that the ORDER BY clause is logically evaluated after the SELECT clause. .

    Of the estimated 1 million SQL developers/DBAs what % knows that? Thanks.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 12:43 PM
    Moderator
  • Kalman, I would love to see this feature as well. It might save us few CTE's and derived tables. However, how would deal with the following dilemma? Don't you think having this feature, even if the logical evaluation order of the code goes according to the parsing order, will cause more problems than it solves?

    CREATE TABLE t (ID INT,Name VARCHAR(20),Name1 VARCHAR(20))
    INSERT INTO t
    SELECT 1,'Test1','Value1' UNION SELECT 2,'Test2','Value2'
    
    
    CREATE VIEW vw_Test
    AS
    SELECT ID,Name,Name1 
    FROM t
    
    SELECT ID,Name1 AS Name
    FROM vw_Test
    WHERE Name = 'Value1'
    --ORDER BY Name DESC
    
    DROP VIEW vw_Test
    DROP TABLE t
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, October 05, 2010 1:26 PM
  • Hi Hugo,

    Can you give a practical AdventureWorks query example which demonstrates the backward compatibility issue?

    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    SELECT ProductID, LEFT(Name, 20) AS Name, ProductNumber
    FROM  Production.Product
    WHERE Name IN ('Front Derailleur Linkage', 'HL Hub', 'Internal Lock Washer 7', 'Road-650 Black, 60');
    


    -- Hugo Kornelis, SQL Server MVP
    Tuesday, October 05, 2010 2:08 PM
  • Ramireddy,

    Obviously parsing starts with SELECT... I am not proposing to change the database engine execution logic. One way to implement is in a preprocessing phase.

    Kalman,

    What do you mean, obviously? Sure, the SELECT statement is first, but query processing isn't a single-pass operation. There is a syntax pass (are the parts in the right places with recognizable meaning), which certainly starts at the top and goes to the end. Then there's a data binding part (do the objects referenced exist, are the functions allowed, are column names uniquely represented), which does NOT have to start with the SELECT. And finally there's an execution plan chosen which is very complex and involves a very definite order of evaluation of each aspect of the query in order to output a final result.

    As you have been told several times in this thread, the order of execution is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, TOP. This maps directly onto the execution plan!

    Update : I originally said that "you'd have to turn the engine completely upside down to allow expressions in the select clause to be used in the WHERE clause." However, I was thinking engine-centrically, when the reality is that the engine simply implements the standard and the standard says to perform the logical evaluation in the order I listed above.

    It seems to me that what you really want is expression aliasing. So let's suggest doing it explicitly instead of implicitly. Then we'll let the developers use it when they want, and not use it when they don't:

    SELECT
       [Year],
       [Month],
       SUM (H.TotalDue) TotalSales
    FROM AdventureWorks2008.Sales.SalesOrderHeader H
    USING
       YEAR (H.OrderDate) AS [Year],
       MONTH (H.OrderDate) AS [Month]
    WHERE [Year] > 2002
    GROUP BY [Year], [Month]
    ORDER BY [Year], [Month]

    Now THIS could work. Now it's clear what those expressions mean. Note that you can't use the expressions in the FROM clause, unless the USING clause can follow each table like the WITH clause now, which could make the query really messy and strange. (What about USING expressions that refer to multiple tables?)

    I can't end without pointing out that the example query isn't very good. It will force a horrible expression-based scan. Instead, the query should use:

    WHERE H.OrderDate >= '20020101'

    Erik

    • Edited by ErikEckhardt Thursday, October 07, 2010 5:22 PM corrected error
    Tuesday, October 05, 2010 5:24 PM
  • Surely Erland.  But only the superexperts know that.  The average SQL developer thinks a query starts with  SELECT...

    I don't think it's only superexperts. I don't know how many copies Itzik's Inside SQL Server books have sold, but he gives a very good rendition of the SELECT statement in his Querying book. I can strongly recommend it.

    The proposed change is improve the productivity of SQL developers without required knowledge about database engine internals.

    This is not about engine internals. It is about how the language is defined. Sure, not all developers care about it. However, since you answer a lot of questions in this forum, one would expect that you have an interest that goes beyond getting the next query to work. After all, if you make the effort to understand how the language works, it will also increase your abilities to write good queries.

    Then again, there are products that permits this. I believe the juniors like Access and FoxPro do this, but also Teradata. I believe the explanation in the latter case is that this is a very old feature in Teradata.

    But, no, it will not happen in Oracle, DB2 or SQL Server. Maybe they can invent some other syntax to define scalar expressions that comes in more naturally than using CROSS APPLY. But none of these players would ever do things that would constitute such a severe blow against backwards compatibility.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, October 05, 2010 8:34 PM
  • Erland,

    Can you repost at the Column Alias thread:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/4ad14cad-1e61-43a0-96f8-70a506106a00

    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 9:02 PM
    Moderator
  • > Can you repost at the Column Alias thread:

    No. I'm sorry, but I don't have the time to go through all threads that are posted in this forum, but only those that happened to be new when I look in, or which meets the scoring criteria in my newsreader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, October 05, 2010 9:13 PM
  • You can not use column alias in WHERE clause in FoxPro (Visual FoxPro). I just verified to be sure.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, October 05, 2010 9:16 PM
    Moderator

  • I also wish the same , and as i have worked heavily on teradata for last two years , and really liked that teradata supports this. And there are many other things thats teradata additionally supports one of the example is below

     

    selelct * from table1 where

    (field1,field2) in (select field1,field2 from table2)

    Muhammad,

    So Teradata supports the Column Alias in the WHERE clause? GROUP BY also?

    I agree also that the multiple fields IN operator is a desirable feature for T-SQL.

     Teradata Column Alias documentation link:

    SELECT departnumber AS d, employeename e, salary
    FROM personnel.employee
    WHERE d IN(100, 500)
    ORDER BY d, e ;

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 9:27 PM
    Moderator
  • But, no, it will not happen in Oracle, DB2 or SQL Server. Maybe they can invent some other syntax to define scalar expressions that comes in more naturally than using CROSS APPLY. But none of these players would ever do things that would constitute such a severe blow against backwards compatibility.

    I have no idea if the proposed change would be light or severe blow to backwards compatibility.

    One or two practical examples from AdventureWorks demonstrating the backward compatibilty issue would be very helpful.

    Thanks.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 9:43 PM
    Moderator
  • SELECT ProductID, LEFT(Name, 20) AS Name, ProductNumber

    FROM  Production.Product

    WHERE Name IN ('Front Derailleur Linkage', 'HL Hub', 'Internal Lock Washer 7', 'Road-650 Black, 60');



    Thanks Hugo. The way I program I would call the alias ShortName, so there would not be naming conflict.

    On the other hand people can program anyway they like, especially beginners, so yes, there would be backwards compatibility issues.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 9:56 PM
    Moderator
  • Kalman,

    You need to watch this video

    Youtube Video


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

     

    Tuesday, October 05, 2010 10:25 PM
    Moderator
  • Thanks Naomi,

    Tradition violation?    There is a revolt going on.... current score 2:17 (I am losing)...

    I would have never guessed that such an innocent change proposal will result in such an opposing reaction. Certainly nice learning experience not only for me but for everybody I hope.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 10:45 PM
    Moderator
  • It was not that innocent :) Although, I think it may be nice to implement but it should be denoted with some specific keyword (may be what Erik suggested with USING), so it will clearly be a new syntax and will not break existing functionality.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, October 05, 2010 10:47 PM
    Moderator
  • It seems to me that what you really want is expression aliasing. So let's suggest doing it explicitly instead of implicitly. Then we'll let the developers use it when they want, and not use it when they don't:
    SELECT
       [Year],
       [Month],
       SUM (H.TotalDue) TotalSales
    FROM AdventureWorks2008.Sales.SalesOrderHeader H
    USING
       YEAR (H.OrderDate) AS [Year],
       MONTH (H.OrderDate) AS [Month]
    WHERE [Year] > 2002
    GROUP BY [Year], [Month]
    ORDER BY [Year], [Month]


    Erik,

    Precisely, expression aliasing!  I like your idea.

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 05, 2010 10:59 PM
    Moderator
  • It was not that innocent :) Although, I think it may be nice to implement but it should be denoted with some specific keyword (may be what Erik suggested with USING), so it will clearly be a new syntax and will not break existing functionality.

     

    Naomi,

    Certainly that appears a promising way to go. Instead of implicit expression aliasing and restricted use, explicit expression alias and usage everywhere in the query where it makes sense.  The backwards compatibility issue becomes mute and developers can program the old way or the new way.

    I am ready to change my proposal if the superexperts agree.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    Tuesday, October 05, 2010 11:29 PM
    Moderator
  • I wasn't talking about the engine, I was talking about a) how relational theory works in combination with the implementation of b) ISO SQL aka the SELECT statement.

    The order is not SELECT then FROM then WHERE, that is like saying buy goods, walk to the shop, choose what I want to buy.

    The order of a SELECT query in relational theory and ISO SQL (note not engine/physical but the documented way it should work) is...

    FROM (forms the set)

    WHERE (restricts the rows/tuples to what you want from formed set from previous step)

    SELECT (project the cols/attributes you want from the restricted set from the two previous steps

    ORDER BY isn't related to set theory, ORDER BY works on the results of the SELECT (the restriction) hence the reason why column aliases work in that ORDER BY clause.

    It would make logical sense for column alias to also work on the GROUP BY which is annoying, but for it to work on the WHERE clause makes absolutely no sense at all unless the column alias is from a proceeding set created by a table expression.

    Hope that helps.

    Tony.

    Wednesday, October 06, 2010 6:50 AM
  • Hi Kalman,

    I don't understand why are you ignoring everybody and still maintaining that the logical ordering of the SELECT statement starts with the projection clause (the SELECT) rather than FROM then WHERE then etc....

    It's just the way the language was originally badly designed.

    Another example is actually setting column aliases, I prefer to say colalias = {expression} but the standard method and the way everybody should be writing it is {expression} AS colalias.

    Anyway, copied from other thread....

    I wasn't talking about the engine, I was talking about a) how relational theory works in combination with the implementation of b) ISO SQL aka the SELECT statement.

    The order is not SELECT then FROM then WHERE, that is like saying buy goods, walk to the shop, choose what I want to buy.

    The order of a SELECT query in relational theory and ISO SQL (note not engine/physical but the documented way it should work) is...

    FROM (forms the set)

    WHERE (restricts the rows/tuples to what you want from formed set from previous step)

    SELECT (project the cols/attributes you want from the restricted set from the two previous steps

    ORDER BY isn't related to set theory, ORDER BY works on the results of the SELECT (the restriction) hence the reason why column aliases work in that ORDER BY clause.

    It would make logical sense for column alias to also work on the GROUP BY which is annoying, but for it to work on the WHERE clause makes absolutely no sense at all unless the column alias is from a proceeding set created by a table expression.

    Hope that helps.

    Tony.

    Wednesday, October 06, 2010 6:55 AM
  • >> Of the estimated 1 million SQL developers/DBAs what % knows that? Thanks.

    In 20 years I've not come across a single person who has had that problem, most dev's thing that a column alias is simply for naming the column on the result set, they don't even realise they can use it on the ORDER BY clause!

    And remember this is not a SQL Server thing - SQL is a standard langauge across many platforms.

    Tony.

    Wednesday, October 06, 2010 6:57 AM
  • I don't understand why are you ignoring everybody and still maintaining that the logical ordering of the SELECT statement starts with the projection clause (the SELECT) rather than FROM then WHERE then etc....

    It's just the way the language was originally badly designed.


    Hi Tony,

    Are you saying that an SQL query should start with FROM like below?

    At any rate, as far as the SQL developer is concerned the database engine is just a blackbox which gives valid output for valid input.

    How do you like Erik's suggestion above, which I like, on explicit expression aliasing?  Thanks.  

    FROM AdventureWorks2008.Sales.SalesOrderHeader
    WHERE YEAR(OrderDate) > 2002
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
    SELECT [Year]=YEAR(OrderDate), [Month]=MONTH(OrderDate), TotalSales=SUM(TotalDue)
    ORDER BY [Year],[Month];

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, October 06, 2010 7:18 AM
    Moderator
  • It would make logical sense for column alias to also work on the GROUP BY which is annoying, but for it to work on the WHERE clause makes absolutely no sense at all unless the column alias is from a proceeding set created by a table expression.

    Hope that helps.

    Tony.

    That is interesting, like below? Actually the WHERE usage (forces table scan) is performance issue unrelated to the SQL language. So WHERE is different from GROUP BY in this regard.

    SELECT [Year]=YEAR(OrderDate), [Month]=MONTH(OrderDate), TotalSales=SUM(TotalDue)
    FROM AdventureWorks2008.Sales.SalesOrderHeader
    WHERE YEAR(OrderDate) > 2002
    GROUP BY [Year],[Month]
    ORDER BY [Year],[Month]
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, October 06, 2010 7:50 AM
    Moderator
  • It would make logical sense for column alias to also work on the GROUP BY which is annoying,

    Well, that depends.

    SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS cnt
    FROM   Orders
    GROUP  BY OrderYear

    That makes sense. but what about:

    SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS cnt
    FROM   Orders
    GROUP  BY OrderYear, Cnt

    but for it to work on the WHERE clause makes absolutely no sense at all unless the column alias is from a proceeding set created by a table expression.

    For a human it is possible to understand

    SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS cnt
    FROM   Orders
    WHERE  Year > 2000
    GROUP  BY OrderYear

    A human would also understand:

    SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS cnt
    FROM   Orders
    WHERE  cnt > 2000
    GROUP  BY OrderYear

    But a computer gets a headache out of this. Try to specify the exact rules when you can use an alias for an aggregate!

    Of course this makes more sense:

    SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS cnt
    FROM   Orders
    GROUP  BY OrderYear
    HAVING cnt > 2000

    But this is even better:

    SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS cnt
    FROM   Orders
    GROUP  BY OrderYear
    QUALIFY cnt > 2000 AND OrderYear > 2005

    The QUALIFY clause is non-standard, but exists in Teradata. It is evaluated after the SELECT clause, why you can use column aliases in it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, October 06, 2010 9:05 AM
  • Kalman

    Would  you expect to refer alias in the below query

    SELECT <columns>, ROW_NUMBER() OVER (ORDER BY col) rn FROM tbl

    WHERE rn>20


     

    Hi Uri,

    Actually I would, the query reads OK for humans.  You need to do a little "trick" programming currently:

    SELECT * 
    FROM (SELECT ProductNumber, 
      Price = ListPrice, 
      ROW_NUMBER() 
       OVER(ORDER BY ProductID) rn 
     FROM AdventureWorks2008.Production.Product) x 
    WHERE rn > 20 
     AND Price > 0.0 
    

    What do you think of Erik's explicit expression aliasing suggestion, I like it, above? Thanks.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

     

    Wednesday, October 06, 2010 12:50 PM
    Moderator
  • >> Of the estimated 1 million SQL developers/DBAs what % knows that? Thanks.

    In 20 years I've not come across a single person who has had that problem, most dev's thing that a column alias is simply for naming the column on the result set, they don't even realise they can use it on the ORDER BY clause!

    And remember this is not a SQL Server thing - SQL is a standard langauge across many platforms.

    Tony.


    I did not verify any of these so this is just a raw list; however, I have recorded a number of these to my cheat sheet over the past 3 or 4 years.  As I indicated previously, this is by no means a rare request.  I do admit that in general, these requests do seem to come from rookies, but here is what I recorded in my cheat sheet:


       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/ef2910d0-6c75-4984-89e7-1a8a86b5bd90/
          Umachandar Jayachandran -- SQL Server 2000

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/8ec35783-116c-49c7-bc35-ccf5452f4939/
          Kent Waldrop
          Louis Davidson
          Mani D

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/369c0848-52db-4eb5-b467-ece70964803b/
          Jens Suessmeyer

       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/c786fab3-bd86-4608-87c3-17a84f10ca6b/
       http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/eb1d8a64-3d9a-400b-a043-3958556809b9/
          Kent Waldrop

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/4ad14cad-1e61-43a0-96f8-70a506106a00
          Kalman Toth
          Kent Waldrop

    Wednesday, October 06, 2010 1:11 PM
    Moderator
  • Suppose, If you evalulate "Select" before where ,group by   , its a lot of wastage of resources. Suppose see the example.

    Ramireddy,

    Quite amazing but your post is not alone worrying about superfast electronic brain wasting time as opposed to the feeble biological brain of an SQL developer not being productive to its fullest capacity.

    Surely both cost money for the employer, however, I am confident Microsoft software engineers can implement improvements to T-SQL in an optimal way.

    As far as the SQL language concerned, all the developer needs to know how to write a valid query. The database engine should be a blackbox.  In an ideal world, the SQL developer need not worry about order of evaluation, execution plans, indexes (database engine should do that), sargable predicates, parameter sniffing, etc... We are not there yet, but that is the goal.

    What do you think of Erik's suggestion of explicit expression aliasing (post above)?  Thanks.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, October 06, 2010 7:58 PM
    Moderator
  • I have no idea if the proposed change would be light or severe blow to backwards compatibility.

    One or two practical examples from AdventureWorks demonstrating the backward compatibilty issue would be very helpful.

    I have already shown you examples, if not from Adventure works. But it's plain and simple:

    CREATE TABLE #t (a int NOT NULL, b int NOT NULL)
    
    SELECT a AS b FROM #t WHERE b = 1

    Sure, the example is silly, but I know that Microsoft takes backwards compatibility seriously. And with good reason. There is another current thread where a poster appears to be upset because @@rowcount is no longer set to 1 when you do EXEC(NULL). You can bet your rear parts, there is plenty of code out there that would break if Microsoft did this change.

    What they could is to introduce a new way to define aliases:

    CREATE TABLE #t (a int NOT NULL, b int NOT NULL)
    
    SELECT a ALIAS b FROM #t WHERE b = 1

    But it would be very confusing with different keywords having different meaning.

    As I pointed out in another post, there would also be some very interesting questions if you could use the alias for an column that comes from an aggregate somewhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, October 06, 2010 9:52 PM
  • > As far as the SQL language concerned, all the developer needs to know how to write a valid query. The database engine should be a blackbox.  In an ideal world, the SQL developer need not worry about order of evaluation, execution plans, indexes (database engine should do that), sargable predicates, parameter sniffing, etc... We are not there yet, but that is the goal.

    Sorry, but that's a whole bunch of ____. Yes, some of these are details that the developer could be relieved from. But some are not. Indexes are of course essential for performance.

    And order or evaluation? That's like saying that a car should be a black box, and the car driver does not need to know the traffic rules! The order of evaluation is not the physical order, but the logical order which defines the meaning of the queries.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, October 06, 2010 9:57 PM
  •  Yes, some of these are details that the developer could be relieved from. But some are not. Indexes are of course essential for performance.

    Erland,

    Why can't the database engine do the indexing automatically? We, humans, are just struggling with it anyhow?

    Thanks.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, October 07, 2010 4:43 AM
    Moderator
  • > Why can't the database engine do the indexing automatically? We, humans,
    are just struggling with it anyhow?

    The question is certainly interesting, and I guess that Microsoft and a few
    more vendors have studied it. But I think there still some work to do there.

    To wit, SQL Server offers the missing_indexes DMVs, and if you use the
    Database Tuning Advisor, you may buy it all its indexes. So what's wrong
    with that?

    Just as there may be too few indexes on a table, there may be too many. An
    index takes up disk space, and adds an overhead to updates, which also
    increases the risk for deadlocks. The missing_indexes DMVs often include a
    number of similar looking indexes like:

    (a, b, c) INCLUDE (d, e)
    (a, b, c) INCLUDE (d, e f)
    (a, b, c) INCLUDE (f)

    Which is due to that there are queries for which these indexes will be
    covering.

    So that is the first part. SQL Server today is a bit too keen on suggesting
    indexes. The other issue is of course, that if you have a mission-critical
    24/7 system, you don't want SQL Server to make the system unavailable
    because it decides to add a few new indexes. In such a system you may only
    want to create index in maintenance windows planned in advance. But that
    also mean that you need the basic indexing to be in place from the
    beginning.

    For a smaller, less critical, system, auto-indexes could work better. But
    that leaves the problem: if developers get used to work with small systems
    where they don't have to bother, who will implement the big critical
    systems?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, October 07, 2010 9:43 PM
  • Thanks Erland for your excellent post on automated database indexing.

    I opened a dedicated thread on this topic:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/186211dc-3611-4042-a3f3-19ba7e5a7fe9


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, October 08, 2010 6:03 PM
    Moderator
  • Great helpful!! Thanks all. 
    Tuesday, February 19, 2013 3:46 AM