6

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

8

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.

3
+50

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.

  • 2
    This was a good guess by Grant at the time he wrote that book, but it is not correct, I'm afraid. – Paul White May 3 '17 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.