Deletes Ignore Check Constraints

I know it sounds a bit odd but DELETE statements really do ignore table constraints. Running the code below in a test database will setup a test to prove this. CREATE TABLE ProductConstraintTest ( ProductID INT IDENTITY(1,1), ProductCode VARCHAR(50), ProductName VARCHAR(100), Cost DECIMAL(19,4), Price DECIMAL(19,4) ); GO CREATE FUNCTION chkProductCount() RETURNS INT AS BEGIN DECLARE […]

Broken Update With Join

Last month I wrote a post titled Broken Left Join which covered an easy to make mistake that I see fairly regularly when reviewing code. This month I’m covering UPDATE statements that run into trouble because of JOINs. Updates with joins are not covered by the ANSI standard. To see the problems that can occur with […]

Broken Left Join

Here is a simple example of a LEFT JOIN that returns the sales stats for all employees under 21. SELECT e.Name, COUNT(s.SaleId) AS Sales, MAX(s.DateOfSale) AS LatestSale FROM Employees e LEFT JOIN Sales s ON e.EmployeeId = s.EmployeeId WHERE e.Age < 21 GROUP BY e.Name This will return all employees under the age of 21 […]