Problem solve Get help with specific problems with your technologies, process and projects.

Adding an ORDER BY clause to a view in SQL Server

Microsoft says that you cannot put an ORDER BY clause in the view definition with SQL Server. True...unless you do the following trick!

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.


 

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close