none
2000 to 2005 SQL Statement Order By Issue

    Question

  •  

    Hi,

      I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:

     

    ALTER  VIEW dbo.SELECT_PP_END
    AS
    SELECT     TOP 100 PERCENT

    PP_PERIOD_ID,

    CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
    FROM         dbo.PP_PERIODS
    ORDER BY PP_END_DATE DESC

     

    The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help

     

    - T.A.


    Monday, July 09, 2007 3:58 PM

Answers

  • Thank you guys,

    I had to do a massive change on my application using "select" statements rather than using the view name.

    I assume views do not output the order by due to execution cost.

    that is the only reasonable conclusion i could think of.

     

    Thanks Again

    T.A.

    Thursday, July 12, 2007 3:40 PM

All replies

  • ORDER BY, inside a VIEW definition 'should' NOT arrange the VIEW output -according the the ANSI SQL standards.

     

    SQL 2000 had an undocumented 'feature' that allowed such to occur. SQL 2005 more closely follows the ANSI standard and does not allow the internal ORDER BY to affect the output (it may still be used internally for a TOP, etc.). This is by design and is as it should be. And it is listed in the documents outlining the differences between SQL 2000 and SQL 2005.

     

    If you wish the VIEW output to be ordered, use an ORDER BY on the SELECT statement that calls the VIEW.

     

     

    Monday, July 09, 2007 4:43 PM
    Moderator
  • Thanks .. I know about the ANSI standard .. I was looking for a work around just like in 2000

    since i am using the view as my datasource across my applications so i want to minimize writing my code

    User Sankar Reddy  offered this work around:

     

    ALTER  VIEW dbo.SELECT_PP_END
    AS
    SELECT     TOP 100 PERCENT
    PP_PERIOD_ID,
    CONVERT(VARCHAR, PP_END_DATE, 101) AS PP,
    rank() over (order by PP_END_DATE desc) as Rank
    FROM         dbo.PP_PERIODS
    ORDER BY PP_END_DATE DESC

     

     

    Thanks Again!

    Monday, July 09, 2007 5:46 PM
  • One of the reasons to write code that follows the documented procedures and 'best practices' is that you don't have applications 'break' for these kinds of reasons.

     

    The question are:

    • When will the VIEW functionality alter again and disrupt your new work-around?
    • At what point will you have more time and cost invested in 'work-arounds' than 'proper' programming would have required?
    Monday, July 09, 2007 5:51 PM
    Moderator
  • Thanks for the advice.

    The whole thing is inherited from an MS-Access application that been upsized to sql server 2000.

    We found the work around for the SQL Server 2000 about 5 or 6 years ago.

    The application is in Access 2003 Adp that has 2000 back end.

    We planning on the transfer in two weeks to SQL 2005.

    I am finding myself writing a huge script to update the master database when its ready to move to the 2005 environment.

    The frustrating issue is why

           " ORDER BY, inside a VIEW definition 'should' NOT arrange the VIEW output -according the the ANSI SQL standards."

     

    I guess it's to give us some work to do.

     

    Thanks again!!!

    Monday, July 09, 2007 6:29 PM
  • Thank you guys,

    I had to do a massive change on my application using "select" statements rather than using the view name.

    I assume views do not output the order by due to execution cost.

    that is the only reasonable conclusion i could think of.

     

    Thanks Again

    T.A.

    Thursday, July 12, 2007 3:40 PM
  • A VIEW does not order the output UNLESS SO INSTRUCTED with an [ORDER BY] clause.

     

    In the situation of using TOP in the VIEW definition, the ORDER BY is required internally to determine the contents of the temporary resultset, but it is not applied to the output of the final resultset.

     

    BECAUSE that is the ANSI standard. Data is neither stored nor retrieved in a sorted order UNLESS specifically requested. That allows the server to best optimize how it stores and retieves the data. Of course, there are exceptions to most 'rules' and in the case of a CLUSTERED INDEX, by happenstance, the retreival 'may' appear to be ordered. But you can't always count on it.

    Thursday, July 12, 2007 4:44 PM
    Moderator