none
Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration rollback

    Question

  • Hi

    We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

    One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

    our client Decided to stop migration, Please some help is there any other alternative.

     


    SNIVAS
    Sunday, October 03, 2010 4:28 AM

Answers

  • Tibor, there is an error in your blog post: you say that it is allowed in SQL 2005, but that is not the case. The bug exists in SQL 2000.

    And indeed it is a bug. The following passes in SQL 2000:

    SELECT name as gulla
    FROM   sysobjects
    ORDER BY kulla.gulla

    That is, you can use any prefix with a column alias, and you will get away with it.

    A similar case is this:

    CREATE TABLE #bludder(a int NOT NULL)
    go
    INSERT #bludder(sture.a) VALUES(23)
    go
    DROP TABLE #bludder

    This incorrect syntax passes even in SQL 2008 SP2. I have an old Connect item for this: https://connect.microsoft.com/SQLServer/feedback/details/311881/sql-server-incorrectly-accepts-prefixes-in-column-list-of-insert-statements


    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
    • Proposed as answer by Naomi NModerator Sunday, October 03, 2010 1:54 PM
    • Marked as answer by SNIVAS Monday, October 04, 2010 10:38 AM
    Sunday, October 03, 2010 11:02 AM

All replies

  • I'm not sure why and where do you have this issue, but the ORDER BY is the only place in the query where you can use column alias. In other words, column alias is supported in the ORDER BY.

    BTW, I think you may enjoy this blog post (one of my favorites):

    http://bradsruminations.blogspot.com/2010/01/trolls-puzzle-sql-fable.html


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, October 03, 2010 4:31 AM
  • http://msdn.microsoft.com/en-us/library/bb510680.aspx

    Did you try considering compatibility level to 80 till all changes are made?


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Sunday, October 03, 2010 5:51 AM
  • I'm not sure why and where do you have this issue, but the ORDER BY is the only place in the query where you can use column alias.

    Agreed. I think we need to see some example to elaborate on this.
    Tibor Karaszi, SQL Server MVP | web | blog
    Sunday, October 03, 2010 8:18 AM
  • Hi Tibork

     

    Right know I don't queries with me to share with you guys.but I can give an exaple

    http://sqlsolace.blogspot.com/2008/05/sql-2005-column-alias-in-order-by.html

    Please see the link...which make my client to rollback


    SNIVAS
    Sunday, October 03, 2010 9:00 AM
  • Tibor

    Perhaps something like that

    create table #t1 (c int)
    insert into #t1 values (1)
    insert into #t1 values (2)

    select col=a.c from #t1 a
    order by a.col

    select 'col'=a.c from #t1 a
    order by a.col

     

     

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 03, 2010 9:23 AM
  • Your original query is wrong in first place. We had couple of those errors that the migration to SQL2008 caught very fast and we had to fix those wrong ORDER BYs. Obviously SQL2000 silently is dropping the prefix in Demographics.Spend to order by alias Spend because the table does not have a column named Spend.

    What we found out is that in some cases on SQL2000 if you order by Demographics.NoSuchColumn the query executes just fine -- no compile error, no run-time error. Migration to SQL2008 just uncovered these bugs in our T-SQL.

    cheers,
    </wqw>

    Sunday, October 03, 2010 9:46 AM
  • Snivas,

    I start typing a log reply here, but it became so ong so I decided to blog it instead. See http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/03/why-can-t-we-have-column-alias-in-order-by.aspx

    Short story is, however that you can't do that anymore (unless you go back in compatibility level for the database, whcih has other disadvantages).


    Tibor Karaszi, SQL Server MVP | web | blog
    Sunday, October 03, 2010 10:30 AM
  • Tibor, there is an error in your blog post: you say that it is allowed in SQL 2005, but that is not the case. The bug exists in SQL 2000.

    And indeed it is a bug. The following passes in SQL 2000:

    SELECT name as gulla
    FROM   sysobjects
    ORDER BY kulla.gulla

    That is, you can use any prefix with a column alias, and you will get away with it.

    A similar case is this:

    CREATE TABLE #bludder(a int NOT NULL)
    go
    INSERT #bludder(sture.a) VALUES(23)
    go
    DROP TABLE #bludder

    This incorrect syntax passes even in SQL 2008 SP2. I have an old Connect item for this: https://connect.microsoft.com/SQLServer/feedback/details/311881/sql-server-incorrectly-accepts-prefixes-in-column-list-of-insert-statements


    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
    • Proposed as answer by Naomi NModerator Sunday, October 03, 2010 1:54 PM
    • Marked as answer by SNIVAS Monday, October 04, 2010 10:38 AM
    Sunday, October 03, 2010 11:02 AM
  • Tibor, there is an error in your blog post: you say that it is allowed in SQL 2005, but that is not the case. The bug exists in SQL 2000.

    Thanks for the correction, Erland.
    Tibor Karaszi, SQL Server MVP | web | blog
    Sunday, October 03, 2010 12:35 PM
  • Hi all,

     

    I convinced my Organization and I going to take it as a challenge...

    I hope you guys with me.


    SNIVAS
    Tuesday, October 05, 2010 8:00 AM