none
Order By sorting error in view on SQL server 2005

    Question

  • Hello.
    I am aware of the issue about random sorting in SQL server 2005, and that there is a hotfix for both the release version of SQL server 2005 and the service pack 2 udated version. The hotfix for the sp2 verision is included in the cumulative update 2 for sp2.

    My question is why is this not included in service pack 3 or the cumulative update 1 for sp3 package for SQL server 2005. I now have an server with these two releases and the views are still unsorted, so how do I get the order by corrected???

    Can i use the update for the sp2 version, or is this not allowed because sp3 is allready installed.

    I really do not know how to fix this without installing a new server with only sp2.

    Thanks in advance.
    Regards
    Rasmus
    • Moved by Tom PhillipsModerator Wednesday, June 08, 2011 1:22 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, March 25, 2009 9:07 AM

Answers

  • Hi,


    There is a restriction on the SELECT clauses in a view definition in SQL Server 200, SQL 2005 and SQL 2008. A CREATE VIEW statement cannot include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement. The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

    However, in the definition of the view, the SELECT statement uses the TOP (100) PERCENT expression or the ORDER BY clause. When you query through the view, query through the view, the result is returned in random order both in SQL Server 2005 and SQL Server 2008 without the specific hotfix. But this behavior is deferent in SQL Server 2000. The hotfix makes you obtain a sorted query result after migrating an application from 2000 to SQL Server 2005.

    Hope this helps.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, March 27, 2009 7:05 AM
  • > Thanks for your post William! I had the same issue as Jobboy in that our SQL server 2005 was already on SP3 and I was trying to determine how to apply the hotfix, but using your information I was able to change the views and get this working without having to request a software change to a legacy application.

    Beware that your application is still broken, and whatever result you get is by pure chance. That could break with the next version of SQL Server or the next service pack.

    The hotfix requires that you have compatibility mode 80, which will not be supported in the next version of SQL Server.

    In other words, while William's fix may have given you some more borrowed time, still need request the application to be changed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 08, 2011 2:17 PM

All replies

  • I am not sure what hotfix you are refering too. 

    But, VIEWS in 2005 are not ordered.  You need to order the result of the view, not inside the view definition.  I do not think this functionality is changed by any hotfix.  This was a change in functionality in 2005 to make it more ANSI compliant.

    Wednesday, March 25, 2009 3:47 PM
  • Yes, after reading alot of articels about this issue I also think that this new behaviour is intended. But I was just confused because there exists this hotfix for both SQL server 2005 and 2008 that fixes the problem: http://support.microsoft.com/kb/926292

    But as you say, I will probably have to make the ordering in my queries. But does this not lower the performance of view lookups compared to the behaviour in SQL server 2000?
    Thursday, March 26, 2009 7:31 AM
  • Rasmus Aas Schram said:

    But does this not lower the performance of view lookups compared to the behaviour in SQL server 2000?

    Rasmus,

    You may consider applying indexed views to address performance issues.  BOL: "Indexed views dramatically improve the performance of some types of queries."

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


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

    Thursday, March 26, 2009 8:59 AM
  • There really is nothing to fix. A view isn't ordered, has never been. A side effect of having both TOP and ORDER BY was in earlier viersions that you often got the rows back in the order of the view's ORDER BY, but in 2005 the optimizer is more advanced than that. since bad adviced floats around, many developers assumed that views could be sorted, so there is a hotfix which *in addition to a trace flag* revert back to the old less sofisticated behavior. I've elaborated on the subject here:
    http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx
    Tibor Karaszi
    Thursday, March 26, 2009 12:37 PM
  • I was not aware of that hotfix to change it back to the 2000 way.

    But, it still is not supported and you should not depend on it working in the future.

    Thursday, March 26, 2009 1:37 PM
  • Hi,


    There is a restriction on the SELECT clauses in a view definition in SQL Server 200, SQL 2005 and SQL 2008. A CREATE VIEW statement cannot include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement. The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

    However, in the definition of the view, the SELECT statement uses the TOP (100) PERCENT expression or the ORDER BY clause. When you query through the view, query through the view, the result is returned in random order both in SQL Server 2005 and SQL Server 2008 without the specific hotfix. But this behavior is deferent in SQL Server 2000. The hotfix makes you obtain a sorted query result after migrating an application from 2000 to SQL Server 2005.

    Hope this helps.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, March 27, 2009 7:05 AM
  • Hi All, 

    as Rasmus Aas Schram said
    "I really do not know how to fix this without installing a new server with only sp2"

    I migrated from SQL 2000 to 2005 and don't want to change application codes.
    So, in short term, I want to apply this hotfix on our system.

    but I cannot apply KB926292 on our cluster system installed SQL 2005 64bit SP3.
    On Featrue Selection page of setup, it displayed this message.

    "This update requires service pack 2. The service pack for product instance MSSQLSERVER is 3. Download the update for service pack 3."

    Could you please tell me how to deploy this hotfix? or
    Should we force the developer team to change the query statement in Application Code?

    However, I will give suggestion to developer to improve the SQL query statement in long term.

    Thanks in all replies,
    JOBBO


    jobboy
    Thursday, April 16, 2009 10:53 AM
  • Hi All,
    for getting ordered results in a view you can use "ORDER BY" with "SELECT TOP (SELECT 100) PERCENT" instead of "SELECT TOP (100) PERCENT"

    William

    Thursday, January 07, 2010 12:42 PM
  • Thanks for your post William! I had the same issue as Jobboy in that our SQL server 2005 was already on SP3 and I was trying to determine how to apply the hotfix, but using your information I was able to change the views and get this working without having to request a software change to a legacy application.
    Wednesday, June 08, 2011 1:13 PM
  • > Thanks for your post William! I had the same issue as Jobboy in that our SQL server 2005 was already on SP3 and I was trying to determine how to apply the hotfix, but using your information I was able to change the views and get this working without having to request a software change to a legacy application.

    Beware that your application is still broken, and whatever result you get is by pure chance. That could break with the next version of SQL Server or the next service pack.

    The hotfix requires that you have compatibility mode 80, which will not be supported in the next version of SQL Server.

    In other words, while William's fix may have given you some more borrowed time, still need request the application to be changed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 08, 2011 2:17 PM