locked
view ignoring order by - used to work in sql 2000 RRS feed

  • Question

  • This works fine in  SQL 2000, but not in SQL 2005

    I have the following view:

    CREATE VIEW [dbo].[viewServicesAll]AS
    SELECT
    TOP 100 PERCENT dbo.services.serviceID, dbo.services.serviceDescription, dbo.services.accountCode, dbo.accountCodes.abbrevName

    FROM dbo.services INNER JOIN

    dbo.accountCodes ON dbo.services.invoiceAccountCode = dbo.accountCodes.codeID

    WHERE (dbo.services.expired = 0)

    ORDER BY dbo.services.serviceDescription



    When I select * from the view, it orders  by the serviceID (which is the primary key of the services table)

    It should be ordering by serviceDescription..... What has changed in SQL 2005 for this to be happening ?

    thanks
    Bruce
    Tuesday, December 13, 2005 3:53 AM

Answers

All replies

  • For saying this i might get some hits... ;)

    But this behavior is "better"... You say that you dont care how the data is ordered. The order by in the view should have no meaning, since that is not the final select.

    The recognizing that the final result does not require an order, the optimizer can skip the step of sorting the result. Bringing a whole bunch of rows in order is some work (Unless its the clustered Index...) and since it was not requested, why should he do it anyway?

    Tuesday, December 13, 2005 4:34 PM
  • Better perhaps - but where is it documented ?

    The create view should give an error if it is going to ignore the order by clause....
    Wednesday, December 14, 2005 2:05 AM
  • Well...

    I think putting an order in a view is the undocumented feature :)

    If you sook at the select statement, then you are told that you should use "order by" if you want the Data in a speciffic order. By leaving the final select out you say "I dont care" about the order...

    Also i hate "select top 100 %" ;)
    Wednesday, December 14, 2005 8:30 AM
  • Hi guys!

    You could solve this problem using "TOP MaxInt" instead of "TOP 100 %".

     

    For example:

     

    CREATE VIEW [dbo].[viewServicesAll]AS

    SELECT TOP 2147483647 dbo.services.serviceID, dbo.services.serviceDescription, dbo.services.accountCode, dbo.accountCodes.abbrevName

    FROM dbo.services INNER JOIN dbo.accountCodes ON dbo.services.invoiceAccountCode = dbo.accountCodes.codeID

    WHERE (dbo.services.expired = 0)

    ORDER BY dbo.services.serviceDescription

     

    2147483647 = equivalent MaxInt number in SQL.

     

    Bye

     

    Monday, June 4, 2007 6:48 PM
  • It's documented in books online. The change in behavior is in the Breaking Changes topic:

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

    The section for ORDER BY in a view definition explains the behavior.

     

     And also under Order By:

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

    Althought it's not explicitly stated, views and tables (relations) in a relational database management system are unordered. So this would all be expected behavior. The order isn't guaranteed without an order by clause in the query.

     

    -Sue 

    Tuesday, June 5, 2007 2:21 AM
  • SQL Server historically allowed explicit ordering of view results using the following syntax

    SELECT TOP 100 PERCENT.....

    FROM ....

    ORDER BY ....

     

    There were KB's stating this would be corrected in SQL 2005 SP2 - cumulative hotfix 3175.

     

    http://support.microsoft.com/kb/926292/

     

    The hotfix did not correct the improper behavior!!! 

     

     

    Tuesday, September 4, 2007 7:19 PM
  • Thanks heaps Gaston,

    I was having this problem too, and it was driving me nuts.  After upgrading from SQL 2000 to 2005, a some of our applicationsreports went all screwy, suddenly there was no ordering.  They were simple reports based straight on views, I guess that isn't the MS way, but I didn't want to have to change them all at the application level.

    You saved me hours Gaston.

    If you put TOP <large number> into the SELECT statement, for whatever reason the query optimizer is fooled and the results are returned sorted as expected, and as they did back in SQL 2000.  An awesome workaround.  Ta.

    Eg.

    SELECT TOP 1000000000
    FROM TableX
    WHERE Something = Something
    ORDER BY ColumnX

    Does indeed sort by ColumnX
    Thursday, November 22, 2007 5:58 AM
  • That it does....

    Unfortunately we changed large views to SELECT TOP 1000000 (they are being used by ms access...) and it placed an immense load on the server. Has anyone else ran into this?

     

    Tuesday, December 4, 2007 9:02 PM
  • We have seen heavy loads placed on SQL Server when linking to tables/views from MS Access in general. I think this reflects more on MSAccess as it doesn't appear to be as much of an issue when using e.g. ADO or sql tools against SQL Server.
    Thursday, October 22, 2009 2:41 PM
  • Thanks very much Dave Jeffrey, that was very useful.

    digitl02, I had the same problem (query would time out) and was able to resolve it by using as few fields as possible in the View.

    In my case I removed all non-numeric fields from the View and was able to work with a 600,000 rows View being able to save orders (when opening the View it opens sorted as set).
    Sunday, July 8, 2012 4:47 AM