April 2005 Blog Posts

  • 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.......