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

StackExchange Database Administrators Q#168730, answer score: 13