Scalar Operator in Seek Predicate

What is the purpose of scalar operator in this seek predicate?

The "Scalar Operator" on the olic.ContainerID column in the question simply indicates that column outer reference is providing a single row (scalar) per iteration of the nested loop (apply). It's purely architectural and nothing to worry about.

Details

Internally, the query processor acts on a tree representation, which at a basic level contains a combination of relational (table-valued) and scalar (single row) operations.

When SQL Server builds showplan output, each operator in the tree (that is enabled for public showplan) is asked to produce a representation suitable for the output target (SHOWPLAN_XML, SHOWPLAN_TEXT, STATISTICS XML...and so on).

The XML output format has to comply with the showplan schema, which contains elements for both relational and scalar operators. The schema specifies scalar operator elements in many places.

Whether the specific text "Scalar Operator(...)" appears in an SSMS tooltip, in the Properties window, or only in the raw XML depends on implementation details at each layer.

For example, the simple query:

SELECT TOP (1) 1;

...produces a plan without "Scalar Operator" in the SSMS tooltips, but present for the Compute Scalar relational operator in the properties window:

...and only in the raw XML for the Top relational operator:

For all practical purposes, the "Scalar Operator" text should simply be ignored. It means nothing except that the thing it encloses is a scalar.

What is the purpose of scalar operator in this seek predicate?

The scalar operator is "as." It is aliasing the column. Now why is this happening? That took a bit of digging and I still really didn't find a 100% concrete answer. The only reference I found to it was in Sql Server Execution Plans, 2nd Edition. The book mentions that this operation in a seek predicate occurs when the optimizer decides to change the order of a join or seek.