SQL Server Performance

View Sorting Problem SQL Server 2005 & Solution !!

Discussion in 'SQL Server 2005 General Developer Questions' started by ricz, Nov 16, 2006.

  1. ricz New Member

    --------------------------It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-------------------



    Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

    It will work until microsoft fix it to work automatically

    Information from
    http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

    RIC

    Software Specialist
    www.rajibul.com
  2. Roji. P. Thomas New Member

  3. jezemine New Member

    Technically, you should not use order by in a view definition. The optimizer reserves the right to ignore the order by clause in a view definition, so if you do it, you can get into trouble.

    Better is to put the order by clause in the query that targets the view. Then you are guaranteed the result set will come back ordered.

    See:

    http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
    http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=79de45cb-2da6-4510-ae43-7e5e42e4c02e


    SqlSpec - a fast, cheap, and comprehensive data dictionary generator
    for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org
  4. ricz New Member

    Yes I agree you for some case. But in context of report development using CR, its better to process the SQL 100% at database site just caz of performance. You have to impl sorting as many group/details as u need. Here my purpose served. Ovbiously this is a bug/limitation of microsoft in a way that they put this feature in sql server 2000 and our company product 2.1 was built in sql server 2000, But all the reports are not working in version 3.1 which is based on 2005. Though it is not written anywhere in microsoft MSDN, rather its written, order by support by TOP 100 PERCENT ??

    In this case, only my way can work for view, if somebody wants to use view directly.

    thanx,for the reply.

    Software Specialist
    www.rajibul.com
  5. Roji. P. Thomas New Member

    Even in 2000, the order by in a view was not documented and guaranteed. Just because it works, you assumed that it'll always works. Never use an undocumented feature in production.

    Roji. P. Thomas
    http://toponewithties.blogspot.com

Share This Page