I'm a little bit confused about how query optimizer eliminates unnecessary columns from view's query when you need to select only specific one.
Here is my view:
CREATE VIEW Schema1.Object1
AS
SELECT Object2.Column1 AS Column2,
Object2.Column3 AS Column4,
Object3.Column5,
Object3.Column6,
Object4.Column1 AS Column7,
Object5.Column1 AS Column8,
Object5.Column9 AS Column10,
Object5.Column11 AS Column12,
Object5.Column13 AS Column14,
Object5.Column15 AS Column16,
Object5.Column17 AS Column18,
Object5.Column19 AS Column20,
Object5.Column21 AS Column22,
Object5.Column23 AS Column24,
CASE
WHEN Object2.Column25 >= Object5.Column25
THEN Object2.Column25
ELSE Object5.Column25
END AS Column25,
Object2.Column26
FROM Schema1.Object6 AS Object2
CROSS JOIN Schema2.Object7 AS Object4
JOIN Schema1.Object8 AS Object3
ON Object3.Column5 = Object2.Column5
AND Object3.Column7 = Object4.Column1
LEFT JOIN Schema1.Object9 AS Object5
ON Object5.Column4 = Object2.Column3
JOIN Schema3.Object10 AS Object11
ON Object5.Column26 = Object11.Column27
Now, I just want to do a query like:
SELECT Column2
FROM Schema1.Object1
...but the estimated plan includes all columns from a view even though the query doesn't return them.
Why has this happened? Can I avoid this?
Here is a link for query plan: https://1drv.ms/u/s!AhdjYi359YDTgYF6sLo8fBsj5H6Ilg
also available at https://www.brentozar.com/pastetheplan/?id=S1MhIB3Ff
Object5
) that is then turned into an inner join by the next join along (Object5.Column26 = Object11.Column27
predicate) but the plan shows a left join.JOIN constant.FlagValuesView AS flag on Object5.Column26 = flag.[Empty]
Where FlagValuesView:CREATE VIEW [constants].[FlagValue] AS SELECT CAST(-1 AS INT) AS [NotUsed], CAST(0 AS INT) AS [Empty], CAST(1 AS INT) AS [Checked]