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 […]

SSRS 2016 – Part 3 Mobile Reports

This is the final post of 3 on SSRS 2016. This time I’m looking at the new SSRS 2016 mobile reports. Post 1 covered the basics of creating your first paginated report. Post 2 covered the new features for the SSRS portal and paginated reports. Mobile Reports To install Mobile Report Publisher, click the down arrow in the SSRS portal […]

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile