How to find out what is represented by the intermediate value used in SQL Server execution plan

Question

I'm learning how to read the actual execution plan in SQL Server. I have noticed that SQL Server tends to represent intermediate value used in the physical query plan as e.g. expr1006, expr1007 etc. (i.e. expr followed by a number).

Here is a screenshot. Notice near the bottom, the expressions expr1006, expr1008, expr1009 listed in the section output list.

It it possible to find out what they really represent?

Answer 1

Right click the compute scalar and choose "properties".

Look in the "Defined Values" section.

You will see something like expr1006 = SomeExpression() if expr1006 is computed there. There may be multiple expressions defined.

Otherwise follow the tree down towards the leaves to find the first place that expr1006 appears and look at the properties of that operator.

For large plans viewing as XML and searching for expr1006 is the quickest way to see where the expression is defined.

The above is usually enough. More recent versions of SQL Server allow you to actually see the values of these expressions by using the query_trace_column_values extended event. More information about that here.