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
Seek Predicate
Best Answer
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:
...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.