byrmol Blog

A “well done“ to the boys and girls who code the SQL Server optimser  is a bit of an understatement.

  A “summarising” view I created contained 3 scalar subqueries.  Working against the view, I found the optimiser ignoring (not resolving) some of the subqueries.  Only a DBA can appreciate the beauty of seeing a correlated subquery removed from an execution plan....

Anyway, below is a little sample of just how “smart” it is...

USE PUBS
GO
--Null aware
SELECT *
FROM dbo.Employee
WHERE Emp_ID IS NULL
--Constant aware
SELECT *
FROM dbo.Employee
WHERE 1=0
--RI Aware
Select *
from dbo.Employee E
WHERE EXISTS (SELECT 1 from Jobs J WHERE J.Job_ID = E.Job_ID)

Look at the plans.... as a side note if Job_ID in the employee table is null then it “plans” as expected.. ... Another reason to avoid nulls

And finally, give the optimiser a chance with “Fake” data hints...Any value outside of your business defined domain is usually good enough.. You do put CHECK constraints on all your columns right?

--SEEK
Select DISTINCT P.Pub_name, stor_name
from publishers P
INNER JOIN Titles T ON T.Pub_ID = P.Pub_ID
INNER JOIN Sales S ON S.Title_ID = T.Title_ID
INNER JOIN Stores SI ON S.Stor_ID = SI.Stor_ID
WHERE P.Pub_ID = 0736 AND T.Title_ID >''