Predicates: in the WHERE clause or the JOIN clause? Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following:


  "The first step in producing such a plan is to normalize each query, which potentially breaks down a single query into multiple, fine-grained queries.  After the Query Optimizer normalizes a query, it optimizes it, which means that it determines a plan for executing that query."


It's been suggested to me by another DBA that performance can be improved for certain queries by moving WHERE clause predicates into the FROM clause, such that:

SELECT *
FROM dbo.table1 t1
    INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID
    LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID
WHERE t1.CreateDate &gt;= '2015-07-31 00:00:00';


would become:

SELECT *
FROM dbo.table1 t1
    INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID
        AND t1.CreateDate &gt;= '2015-07-31 00:00:00'
    LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID;


Apparently the implication for the first example is the query optimizer will perform the JOIN first, then apply the WHERE clause, which for complicated queries with large tables will not perform as well as if we manually re-write the query into the form presented in the second example.  Apparently this only applies to outer joins, i.e. if this were an inner join, the normalizer would in fact move the WHERE predicate into the FROM clause.

Can someone confirm this behavior?  Is there some documentation somewhere I can look at?  Can I see this in the execution plan?



1 - yes, I know this is ancient.
I guess this is one of the "It depends"-moments.
In a regular execution there is no or at least nearly no difference.

Thought it will become more interesting once the queries are more complex.
More complex scenarios are e.g.:


Nested Joins
FORCE ORDER  (Query Hint)


I would have added LEFT and CROSS Joins as well, thought they are already part of your example.

About the "experience in SQL Server normalization" - I'd say, besides looking in the code, there will not be much you can hold your self on. It is often just a "It depends!"