Check out May 16th comments on blog post http://michaeljswart.com/2011/06/forget-about-pivot/
Brad Schulz provides a case where a view on a pivot statement uses a left join. When querying the view, the join is redundant, but not eliminated by the query optimizer.
This is surprising. I would have guessed that SQL Server would remove the unnecessary join during the simplifications step.
either can't be simplified or won't
By mjswart 146 asked May 28, 2013 at 02:50 PM
There are a number of stages at which the optimizer can remove unnecessary joins:
Also, there are two main reasons for a join to be regarded as 'necessary':
Projecting new columns includes the case where an outer join introduces
In the example given, and after expanding the view definition, the query is:
Looking at the two reasons a join can be regarded as necessary in turn, we see that point (1) does not apply, since we are projecting only columns from the
Therefore, point (2) must apply: the join affects the cardinality of the result set. Now, the use of
The only remaining possibility is that performing the join would result in more than one row in the result set per customer. Clearly this cannot be the case, however, since the expansion of the
And this is where the issue arises. When properties are derived for the
If the query is changed to add a
...(or an equivalent outer
By SQLkiwi ♦ 6.6k answered May 28, 2013 at 11:34 PM
I can't see the SQL inside the view or the DDL but can SQL reliably determine the cardinality of the JOIN? If you join phones to customers and only need customers you would still get a potential one to many in the output. Perhaps the the PIVOT command isn't in the list of simplification rules for this type of join reduction.
By Dan Holmes 725 answered May 28, 2013 at 03:14 PM