Adding an ORDER BY clause to a view in SQL Server

the query. Many people think that if that's true then you cannot "Order" the whole query but only the top few. However, this is not true because when you use TOP 100 PERCENT with the query you can ORDER BY the whole result set inside the view definition, like this (I tested this on a NT4 workstation and on NT4 server SP6, with SQL Server versions 7 and 2000):

CREATE VIEW  OrdersDetailsPlusOrderBy 
AS 
SELECT 
        TOP 100 PERCENT 
        OrderId, 
        ProductId, 
        UnitPrice, 
        Quantity 
FROM 
        northwind..[order Details] 
ORDER BY 
        UnitPrice DESC

Reader Feedback

B.B. writes: Because of performance implications, using TOP/ORDER BY in a view is generally not a good idea. A better way of accomplishing this is first creating the view as:

create view vw_orderdetails as select OrderId, ProductId, UnitPrice, Quantity 
FROM [order details] go

Then write a select statement such as:

SELECT TOP 100 PERCENT orderid, productid, unitprice, quantity 
FROM vw_orderdetails 
ORDER BY unitprice DESC

N.N. writes: "First, creating a view from the GUI will automatically add in the Top 100 Percent clause if there is an Order By clause. You do not have to do anything. (Sometimes a warning message will appear, but the clause will still be added.) Second, in terms of efficiency, there is no difference with or without the Top 100 Percent clause. You can easily check this through the Execution plan. I have tried this with different tables and multi-join selects.

For More Information

• What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.