July's Bug Solution: Using SQL Server 2000, you want to query the Orders table in the Northwind database and return the following attributes for each order: CustOrder (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 sorting. To perform these tasks, you execute the following code:

USE Northwind;

SELECT
  IDENTITY(int, 1, 1) AS RowNum,
  CustomerID + CAST(OrderID AS NVARCHAR(10)) AS CustOrder,
  OrderDate,
  EmployeeID
INTO #O
FROM dbo.Orders
ORDER BY CustOrder;

SELECT * FROM #O ORDER BY RowNum;

To your surprise, the query doesn't calculate the RowNum values based on CustOrder ordering. (Note that if the row numbers do correspond to the CustOrder sorting when you run this code, this is non-guaranteed behavior.)

The bug here is that when you use a SELECT INTO statement, SQL Server 2000 doesn't guarantee that it will sort the rows before generating the identity values. Based on query cost estimates, the query optimizer decides whether to sort before or after generating the identity values. If the optimizer sorts after generating the identity values, the identity values won't reflect the order you specify in the ORDER BY clause. (You can read more about this behavior in the Microsoft article “The behavior of the IDENTITY function when used with SELECT INTO or INSERT..SELECT queries that contain an ORDER BY clause” at http://support.microsoft.com/?kbid=273586.)

To guarantee that the identity values reflect the order you specify in the ORDER BY clause, you need to create the temporary table #O with an identity column called RowNum, then use an INSERT SELECT statement that contains an ORDER BY clause. The INSERT SELECT statement will populate the temporary table and generate the identity values in the desired order. The following code produces the solution you're after:

USE Northwind;
GO
IF OBJECT_ID('tempdb..#O') IS NOT NULL
  DROP TABLE #O;
GO
CREATE TABLE #O
(
  RowNum INT NOT NULL IDENTITY PRIMARY KEY,
  CustOrder NVARCHAR(15) NOT NULL,
  OrderDate DATETIME NOT NULL,
  EmployeeID INT NOT NULL
)


INSERT INTO #O(CustOrder, OrderDate, EmployeeID)
  SELECT CustomerID + CAST(OrderID AS NVARCHAR(10)) AS CustOrder,
   OrderDate, EmployeeID
  FROM dbo.Orders
  ORDER BY CustOrder;

SELECT * FROM #O ORDER BY RowNum;

Alternatively, in SQL Server 2005, you can use the ROW_NUMBER() function to generate sequential integers in a desired order instead of relying on the IDENTITY column property:

USE Northwind;
GO
IF OBJECT_ID('tempdb..#O') IS NOT NULL
  DROP TABLE #O;
GO
WITH MyOrders AS
(
  SELECT CustomerID + CAST(OrderID AS NVARCHAR(10)) AS CustOrder,
    OrderDate, EmployeeID
  FROM dbo.Orders
)
SELECT ROW_NUMBER() OVER(ORDER BY CustOrder) AS RowNum, *
INTO #O
FROM MyOrders;


SELECT * FROM #O ORDER BY RowNum;

August's Bug: You want to randomly assign shifts to employees, so you write the following query to sort a list of employees from the Northwind database's Employees table in different random order every time you run the query:

SELECT * FROM Northwind.dbo.Employees ORDER BY RAND();

However, after running the query several times, you find that it keeps returning the employees sorted the same way—not randomly. What's wrong?