Microsoft says that you cannot add an ORDER BY to a view unless it comes with a TOP clause at the beginning of...
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
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 firstname.lastname@example.org 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.