Switch

Introduction

The Switch operator reads and returns all rows from exactly one of its inputs. The Predicate property determines which of its inputs is used.

This operator is very rare; I have thus far only seen in used for a single-row insert into a partitioned view, and for some XQuery related execution plans.

The Switch operator has two or more inputs. There is no known maximum. The one that appears first in the execution plan XML is considered input number one, the second is number two, and so on. In the graphical execution plan, input one is listed at the top, input two is the second from the top, etcetera.

Visual appearance in execution plans

Depending on the tool being used, a Switch 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 Switch 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 Switch 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 Switch has less than 2 inputs.

Input and output mapping

In all the execution plans with a Switch operator that I have seen, the Switch returns no data (zero-column rows) to its parent. All attempts to construct an execution plan where Switch does return data have failed so far.

However, I still believe that the Switch operator is in principle able to return data to its parent, as indicated by the presence of the Output List and Defined Values properties. I assume that the Defined Values property is used to map columns from each input to the corresponding output columns, using the same format as the Defined Values property of a Concatenation operator.

If this assumption is correct, then format of the Defined Values property in the execution plan XML is one DefinedValue node per output column, and within that node are n + 1 ColumnReference nodes. The first ColumnReference specifies the name of the output column; ColumnReference 2 through n + 1 specify the mapped input column from input 1 through n. All input columns that are mapped to the same output column must have the same data type. In Management Studio, the Defined Values predicate would be displayed in a slightly more readable way, as output column = (input column 1;…; input column n).

Input and output mapping

The columns in the output of a Switch operator, as specified in its Output List property, are typically given a generated name. As all generated column names in execution plans, these names are formed by combining a mnemonic (usually “Union” for a Switch operator) and a 4-digit number that is unique within the execution plan.

The Defined Values predicate of Switch specifies, for each of the columns in the Output List, what column from each of the inputs is mapped to that output column. In the execution plan XML, the format of the Defined Values property for Switch is one DefinedValue node per output column, and within that node are n + 1 ColumnReference nodes. The first ColumnReference specifies the name of the output column; ColumnReference 2 through n + 1 specify the mapped input column from input 1 through n. All input columns that are mapped to the same output column must have the same data type. In Management Studio, the Defined Values predicate it displayed in a slightly more readable way, as output column = (input column 1;…; input column n).

Order preservation

Since the Switch operator only returns data from a single one of its inputs, unchanged, the order of data returned by Switch will always be equal to the order of that input. However, the optimizer cannot predict which input will be executed, so if the inputs have different sort orders, the output of Switch has to be considered unordered.

When all inputs to Switch are guaranteed to be in the same sort order, then the output of Switch is also guaranteed to be in that order. However, it is at this time not known whether the optimizer recognizes this special case, or whether the optimizer simply considers the output of Switch to be unordered in all cases.

Of course, when Switch returns no data (empty rows), then the order is undefined and irrelevant anyway.

Operator properties

The properties below are specific to the Switch 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 Switch operator are marked with a *.

Property name	Description
Defined Values *	The Defined Values property of a Switch includes, for each column in the Output List property, an XML element that lists the name of the output column, followed by the name of the mapped input column from each input. See also the main text.
Predicate	An expression that evaluates to integer value. This is a zero-based value that indicates which input to execute, so a Switch with three inputs will have a Predicate that can evaluate to 0, 1, or 2; resulting in the execution of input 1, 2, or 3 respectively.

Implicit properties

This table below lists the behavior of the implicit properties for the Switch operator.

Property name	Description
Batch Mode enabled	The Switch operator supports row mode execution only.
Blocking	The Switch operator is non-blocking.
Memory requirement	The Switch operator does not have any special memory requirement.
Order-preserving	The Sort operator imposes the order as specified in the Order By predicate on the data it returns.If all inputs to the Switch are ordered by the same column(s), then the output is also guaranteed to be ordered by that/those column(s). It is unknown whether the optimizer actually treats the output as ordered, though. See also the main text.
Parallelism aware	The Switch operator is not parallelism aware.
Segment aware	The Switch operator is not segment aware.