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:

SQL Server Management Studio		Azure Data Studio	Plan Explorer
(version 17.4 and up)	(until version 17.3)

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.

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

There are no properties that are specific to the Sequence operator. Most of the Common properties apply as normal, and without specific meaning for the Sequence operator.

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.