TOP (100) PERCENT when designing a view 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
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