Sequence

Introduction

The Sequence operator reads all rows from all its inputs, in order, and without modification. It returns all rows it reads from its last input only.

This operator is typically used in execution plans where some branches are needed to do preparation work (e.g. to invoke a Table Valued Function operator) and then other branches produce the final output, or multiple branches need to do work in sequence (such as in wide data modification plans). The operators on the non-last inputs often do not produce any output at all, but even if they do the rows returned are discarded. Only rows returned from the last input are returned to the parent of the Sequence operator.

The Sequence operator has two or more inputs. There is no known maximum. The inputs are processed in the order in which they appear in the execution plan XML, which corresponds to the order in which they are shown (top to bottom) in the graphical execution plan.

Visual appearance in execution plans

Depending on the tool being used, a Sequence operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Sequence operator is as shown below:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.

Also note that while the flowchart might suggest that Sequence can work with just a single input, there is no way to guarantee it actually does because the optimizer never produces an execution plan where Sequence has less than 2 inputs.

Non-last inputs

For all inputs except the last, the Sequence operator ignores whatever data its child operator returns in response to the GetNext() call. Depending on the exact operators used, it is not uncommon to see operators that don’t return anything at all. This is for instance possible with the Table Valued Function operator, which can be set to only execute its logic and then simply return an end of data signal when done. Other operators, such as for example data modification operators in a wide plan, might still return rows if they have no logic to avoid returning rows; the optimizer will where possible minimize overhead by setting the Output List property of those operators to empty, so that only row headers are returned.

Second-last input

In SQL Server 2019, the algorithm for the Sequence operator was amended to provide support for evaluating a SHORTEST_PATH expression. This Sequence can be recognized by the presence of the Is GraphDB Transitive Closure and GraphSequenceIdentifier properties.

When the Is GraphDB Transitive Closure property is present and true, then the second-last input of the Sequence can be executed multiple times. At the end of each execution, it checks the graph worktable with data for the next iteration (this is the worktable with GraphWorkTableIdentifier equal to the GraphSequenceIdentifier of this Sequence operator, and with GraphWorkTableType equal to 1). If this table is not empty, then it empties the graph worktable with data for the current iteration (the worktable with the same GraphWorkTableIdentifier, but GraphWorkTableType equal to 0), swaps it with the graph worktable with data for the next iteration, and then executes the second-last input again.

This repeats until an execution of the second-last input leaves the worktable with data for the next iteration empty. At that point, the Sequence operator moves on to the last input.

For a very detailed explanation of this process, please refer to this blog post.

Last input

For the last input only, the Sequence operator does returns rows to its parent operator. The columns to return are specified in the Output List property. Although the Sequence operator looks in many ways similar to Concatenation, it actually behaves very different in this area. Concatenation returns data from multiple inputs in the same output, and column names might not be the same in all inputs, so Concatenation requires a column mapping in the Defined Values property. For Concatenation, since all output relates to data from its last input, columns in Output List are simply matched by name between that last input and the Output List property.

Note that in some cases, especially for wide data modification plans, the last input might also return empty rows, or even no rows at all. In that case, Sequence simply returns those same empty rows to its parent, or no rows at all.

Operator properties

The properties below are specific to the Sequence operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Sequence operator are marked with a *.

Property name	Description
Is GraphDB Transitive Closure	When this property is present and set to True, the second-last input to Sequence will be executed multiple times, until an execution ends with no rows in the worktable with data for the next iteration. See also the heading “second-last input” in the main text. Introduced in SQL Server 2019.
GraphSequenceIdentifier	This property stores a zero-based counter that increases for each Sequence operator in the execution plan that is used for a SHORTEST_PATH computation. It is used to identify which set of graph worktables is used by this Sequence operator. See also the heading “second-last input” in the main text. Introduced in SQL Server 2019.

Implicit properties

This table below lists the behavior of the implicit properties for the Sequence operator

Property name	Description
Batch Mode enabled	The Sequence operator supports row mode execution only.
Blocking	The Sequence operator can be considered semi-blocking. It does not return any rows while executing the first n – 1 inputs, but becomes a fully streaming operator once it starts on the final input.
Memory requirement	The Sequence operator does not have any special memory requirement.
Order-preserving	The Sequence operator is fully order-preserving for its last (lowest) input. Data returned by other inputs (if any) is ignored, so their order is no longer relevant for parent operators of the Sequence operator.
Parallelism aware	The Sequence operator is not parallelism aware.
Segment aware	The Sequence operator is not segment aware.