Optimiser Magic
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 PUBSGO--Null awareSELECT * FROM dbo.EmployeeWHERE Emp_ID IS NULL--Constant awareSELECT * FROM dbo.EmployeeWHERE 1=0--RI AwareSelect *from dbo.Employee EWHERE EXISTS (SELECT 1 from Jobs J WHERE J.Job_ID = E.Job_ID)
Look at the plans.......