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
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 tdichiara@techtarget.com 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.
