6

When designing a view (adding Order By clause) the wizard in SSMS automatically adds

TOP (100) PERCENT

What do we need it for? Are there any performance consideratons regarding TOP (100) PERCENT?

Thank you

12

ORDER BY is not allowed on VIEWS unless you set a TOP() clause.

The wizard adds it automatically just to avoid the error.

Have a look at CREATE VIEW on MS Docs.

The SELECT clauses in a view definition cannot include the following:

  • An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
    Important:  
    The ORDER BY clause is used only to determine the rows that are returned by the 
    TOP or OFFSET 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.
  • The INTO keyword
  • The OPTION clause
  • A reference to a temporary table or a table variable.

You should add ORDER BY clause in your query:

SELECT   F1, F2, F3, F4
FROM     YOUR_VIEW
ORDER BY F1, F2
  • 1
    I'm glad to be able to help. – McNets Mar 31 '17 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.