TOP (100) PERCENT when designing a view

Question

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

Answer 1

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