Sql-server – Scalar Operator in Seek Predicate

execution-plansql serversql-server-2012

I have following simplified version of my actual query, in SQL Server 2012. It has a scalar operator in seek predicate when selecting data from Containers table.

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

CREATE TABLE #EligibleOrders (OrderID INT PRIMARY KEY,
                             StatusCD CHAR(3),
                              CreatedOnDate DATETIME
                              )
--insert logic into #EligibleOrders

--Final Query
SELECT T2.OrderID ,olic.LineItemID,
        SUM(c.quantity) AS ShippedQty,
        COUNT(DISTINCT c.ContainerID) AS ShippedCases
FROM #EligibleOrders T2
INNER JOIN dbo.OrderLineItemContainers (NOLOCK) AS olic 
    ON  olic.OrderID = T2.OrderID
INNER JOIN dbo.Containers (NOLOCK) AS c
    ON olic.Containerid = c.Containerid
GROUP BY T2.OrderID ,olic.LineitemID 
OPTION (MAXDOP 1)

Execution Plan

enter image description here

Seek Predicate

enter image description here

Best Answer

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:

Compute Scalar Scalar Operator

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

Top Scalar 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.