June's Bug Solution: You use SQL Server 2005 to create the followingVSortedOrders view in the Northwind database:
CREATE VIEW dbo.VSortedOrders
SELECT TOP(1ØØ) PERCENT
CustomerID, OrderID, OrderDate
ORDER BY CustomerID, OrderID;
You then issue the following query:
You expect to get the data sorted by customer and order ID, but you don't.Where's the bug, and what's the solution?
The bug is both in designing such code and in the expectations from the code—to get the data sorted. Remember that a view is supposed to represent a table—a set—and a set has no predetermined order to its rows. SQL Server isn't bound to return the output from the outer query in any particular order because the outer query has no ORDER BY clause. Specifying TOP(100) PERCENT, of course, means all rows. SQL Server 2005's optimizer realizes that all rows are requested and that the ORDER BY clause in the view's query has no logical meaning, so it ignores both the TOP and the ORDER BY clauses. Interestingly, SQL Server 2000's optimizer does sort the data in a TOP 100 PERCENT request, but the behavior of SQL Server 2005 is correct. In short, if you want data from the view sorted in the output, specify an ORDER BY clause in the outer query and avoid using TOP(100) PERCENT and an ORDER BY clause in the view's query.
July's Bug: Using SQL Server 2000, you want to query the Orders table in the Northwind database and return the following attributes per each order: Cust-Order, which is a concatenated value of the CustomerID and OrderID source columns, and the OrderDate and EmployeeID columns.You also want to calculate a row number based on CustOrder ordering.You run the following code:
IDENTITY(int, 1, 1) AS RowNum,
CustomerID + CAST(OrderID AS
NVARCHAR(10)) AS CustOrder,
ORDER BY CustOrder;
SELECT * FROM #O ORDER BY