Create a view order by error The ORDER BY clause is invalid in views RRS feed

  • Question

  • Hi I am trying to create a view . But sql need to add order by . When add "order by" then error as follows 

    Msg 1033, Level 15, State 1, Procedure tblJobDetails1_vw, Line 41 [Batch Start Line 0]
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    When create view with   select  TOP (100) PERCENT, then able to create. But not sure why. Any alternative to create a view with "order by " and not use "TOP (100) PERCENT,"

    Create view in 2016 database and access 2008 DB through linked server 

    real data is on 2008 version 

    Secondly why cant use OPTION (RECOMPILE) option on views 



    • Edited by ashwan Tuesday, August 6, 2019 2:21 AM
    Tuesday, August 6, 2019 1:57 AM


All replies

  • Try avoid to use ORDER BY within a view, why not just 

    SELECT * FROM view ORDER BY  col...

    In my opinion one of the reason is  to address performance  problem, think if you  specify order by with in a view  and then while calling you a user specify another column in order outside the view, 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 6, 2019 4:01 AM
  • Hi ashwan,

    ORDER BY clause is used in 2 different contexts in SQL – the programming language itself:
    1.With the ORDER BY clause you can define in which sort order the rows are returned back to your client application
    2.In addition the ORDER BY clause is used to define which rows are returned from a TOP expression.

    The most important thing you have to know is that you define a so called Set with a view, inline functions, derived tables, sub queries, and common table expressions. A set is a mathematical concept, which is part of the Set Theory on which relational databases – like SQL Server – are based. And a set is by its nature unsorted. Therefore you are not allowed to use the ORDER BY clause in combination with a view definition – as you have seen above. If you try to do it, SQL Server disallows that operation and gives you an error message.

    Please refer to The ambiguity of the ORDER BY in SQL Server to get more information.

    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 7, 2019 9:05 AM