August's Bug Solution: 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?
SQL Server invokes n on-deterministic functions such as RAND() and GETDATE() once for the entire query, not once for each row. Thus, all rows get the same random value, so the ORDER BY clause becomes meaningless. The only exception to this rule is the NEWID() function, which SQL Server invokes once per row instead of once per query. To get a different random value for each row with good random distribution, use the expression CHECKSUM(NEWID()) instead of RAND():
SELECT * FROM Northwind.dbo.Employees ORDER BY CHECKSUM(NEWID());
September's Bug: Using the Employees table in the Northwind database, you want to return the employee IDs of all employees who aren't managers—that is, employee IDs that don't appear in the ReportsTo column. You write the following query:
USE Northwind; SELECT EmployeeID FROM dbo.Employees WHERE EmployeeID NOT IN (SELECT ReportsTo FROM dbo.Employees);
But to your surprise, you get an empty set. Where's the bug in the code?
