Catch That Bug! - 22 Jun 2006

June's Bug Solution: You use SQL Server 2005 to create the followingVSortedOrders view in the Northwind database:

USE Northwind; 
CREATE VIEW dbo.VSortedOrders 
  CustomerID, OrderID, OrderDate 
FROM dbo.Orders 
ORDER BY CustomerID, OrderID; 

You then issue the following query:

SELECT CustomerID, OrderID,
FROM dbo.VSortedOrders;

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:

USE Northwind;

  IDENTITY(int, 1, 1) AS RowNum, 
  CustomerID + CAST(OrderID AS 
  NVARCHAR(10)) AS CustOrder, 
FROM dbo.Orders 
ORDER BY CustOrder;
To your surprise, the RowNum values aren't calculated based on CustOrder ordering. Can you identify the bug in the code?
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.