Post reply

sql 2005 gotchas

  • February 27, 2006 at 7:00 am

    #113418

    This query works in sql 2000 but not in 2005.  If you qualify the order by as in order by organization.organization_id it does work in 2005.  I could see it
    if a join was involved with duplicate fields in both tables, but here the results would have the same field twice from the same table.

    select organization_id, * from organization  order by organization_id

     

    Msg 209, Level 16, State 1, Line 1

    Ambiguous column name 'organization_id'.

    I need to spend some more time looking at deprecated features but this may simply fall into the bug category.

    http://msdn2.microsoft.com/en-us/library/ms143729.aspx

     

  • February 27, 2006 at 3:46 pm

    #623558

    This is correct. I tested. You have to add a table name in Order By then it works. I compared estimated execution plans for both 2000 and 2005. In 2000 if you would show estimated execution plan and put you mousepointer over the SELECT element in the picture and look in the Argument portion of the yellow tooltip square you will see that it says "Order by 1". And in 2005 if you do the same you will see "Order by Person.Contact.ContactID" (I used AdventureWorks.Person.Contact table for example since I don't have your table) Then I just typed a query like that and it worked:

    Select ContactID,* from Person.Contact Order By 1

    Also worked:

    Select ContactID as CI,* from Person.Contact Order By ContactID

    So they changed the processing internally from "Order By 1" to "Order By ColumnName". I thinkitmay be related to Dot Net nature of SQL Server 2005 where things are processed using DataSets and DataTables

     

    Regards,Yelena Varsha

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply