locked
SQL Views-sorting issue in SQL 2008 RRS feed

  • Question

  • Hi Guys,

    We are migrating from SQL 2000 to 2008R2

    While testing,we have encountered a problem with SQL views sorting. SQL views are behaving differently in SQL 2008. i read in blogs that 'Order by' doesn't support in SQL 2005 and higher versions.

    Work done to resolve this issue:

    We have tested views with Top 99.99 % and it works fine for us.(confused about logic behind this)

    - Large number of views are being used in Application, so we can't change thousand of views with Top 99.99%.

    I'm not interested in change large number of views. So please let me know, if there is hotfix for this problem.


    Rajan

    Thursday, August 8, 2013 5:34 AM

Answers

  • there is a hot-fix available on Mircrosoft

    Hello Rajan,

    That's not right; this hotfix won't fix your problem, it's just allows you to use a TOP 100 PERCENT, which didn't work in 2005; here you have had to use 99.99999 PERCENT as a work-around. And there is no "hotfix" for 2008R2 or 2012 available, because it's already implementes in the database engine; no fix required.

    But also with the fix you still must add the TOP clause to the view to use a ORDER BY in a view; no way around this.

    CREATE VIEW dbo.MyTop100View
    AS
        SELECT TOP 100 PERCENT name, type_desc
        FROM sys.objects
        ORDER BY name;
    GO
    
    SELECT *
    FROM dbo.MyTop100View;


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Fanny Liu Wednesday, August 14, 2013 12:52 PM
    • Marked as answer by Fanny Liu Thursday, August 15, 2013 10:52 AM
    Thursday, August 8, 2013 7:07 AM

All replies

  • You need to explicitly give the Sorting outside the view.

    Select * From VW_table Order by Col1 [asc/desc]


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 8, 2013 5:39 AM
  • So please let me know, if there is hotfix for this problem

    Hello Rajan,

    There is no hotfix for it available, because it is not an issue.

    It makes no sense to use an order by in a view, because the caller have / will define the order how he what's to get the data. Also other RDBMS don't support an order clause in a view definition.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 8, 2013 5:59 AM
  • Hi Latheesh,

    Thanks for your quick response.

    It will be time-consuming and difficult task for us because application is quite much old(Ms-Access) and they are using inline queries behind the scene.

    So, i was looking for one-go solution for this.


    Rajan

    Thursday, August 8, 2013 6:51 AM
  • Hi Olaf,

    Reason, i'm looking for Hotfix,there is a hot-fix available on Mircrosoft website which refers SQL 2008 but not for SQL 2008 R2.

    I don't have link this time,but i'll post it later.


    Rajan

    Thursday, August 8, 2013 6:54 AM
  • there is a hot-fix available on Mircrosoft

    Hello Rajan,

    That's not right; this hotfix won't fix your problem, it's just allows you to use a TOP 100 PERCENT, which didn't work in 2005; here you have had to use 99.99999 PERCENT as a work-around. And there is no "hotfix" for 2008R2 or 2012 available, because it's already implementes in the database engine; no fix required.

    But also with the fix you still must add the TOP clause to the view to use a ORDER BY in a view; no way around this.

    CREATE VIEW dbo.MyTop100View
    AS
        SELECT TOP 100 PERCENT name, type_desc
        FROM sys.objects
        ORDER BY name;
    GO
    
    SELECT *
    FROM dbo.MyTop100View;


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Fanny Liu Wednesday, August 14, 2013 12:52 PM
    • Marked as answer by Fanny Liu Thursday, August 15, 2013 10:52 AM
    Thursday, August 8, 2013 7:07 AM
  • Hi Olaf,

    Thanks for this information.

    In brief, there is no other way to get rid of this situation. Only workaround is to Alter every single view.

    One more concern, Is there any possibility that 99.99% revert wrong sorted results. because i read in some blogs that if we use INNER JOIN with in view having Order By Clause. It may react differently.

    If this is the case, then what are the steps which i need to keep in mind while modifying these views.


    Rajan

    Thursday, August 8, 2013 10:15 PM
  •  Is there any possibility that 99.99% revert wrong sorted results.

    Hello Rajan,

    I never heard it could return wrong sorting; but it may not return all rows, only 99,99%; therefore the fix to be able to use TOP 100 PERCENT to get all rows.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 9, 2013 4:28 AM