Constant Scan

Introduction

The Constant Scan operator is used to inject rows in an execution plan that have no external source. The rows to be returned by this operator are stored in its Values property.

Visual appearance in execution plans

Depending on the tool being used, a Constant Scan 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 Constant Scan 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.

Read first row / Read next row

All data to be returned is taken from the operator’s Values property. For the “Read first row” action, a row is formed where for each column in the Output List, the value is set to the corresponding Column element within the first Row element of the Values property. For the “Read next row” action, the same is done for the Row element that immediately follows the last one processed.

End of data?

A Constant Scan operator does not fetch data from a child operator, nor from the storage subsystem, so it will not receive an end of data signal from its child operator or from the storage subsystem. Instead, it generates its own end of data signal when the “Read next row” action is invoked but the last Row element processed was the last Row element in the Values property, or when the “Read first row” action is involved while there are zero <Row> elements in the Values property.

Values property

The Values property lists the rows to be returned. In the (undocumented and inaccessible) internal representation of the execution plan, the Values property consists of an ordered list of zero or more Row elements, and each Row element consists of an ordered list of exactly as many Column elements as there are columns listed in the Output List property. Each Column element can be a constant value, a variable, or a simple expression based on one or more constant values and/or variables.

The Constant Scan operator supports returning “empty” (zero-column) rows. This is represented in the internal representation by having the correct amount of Row elements that each have no Column elements. The Constant Scan operator also supports returning zero rows (for instance in execution plans for queries that have a fallacy in the WHERE clause); this is represented in the internal representation by having no Row elements within the Values property.

The representation of the Values property in the execution plan depends on what representation of the execution plan you look at. The XML representation, which is the representation that SQL Server sends to a client when an execution plan is requested, represents each Row element as a <Row> node directly under the <Values> node. Each Column element is then represented as a <ScalarOperator> node under the corresponding <Row> node.

Since the XML schema for the execution plan does not allow either a <Value> node or a <Row> node to be empty, the Values property cannot be correctly represented for a Constant Scan that returns empty rows or no rows. Empty rows would result in <Row> nodes with no < ScalarOperator > nodes; no rows would result in a <Values> node with no <Row> nodes. In both of these cases, the <Values> node is completely omitted from the execution plan XML.

The graphical execution plan in SQL Server Management Studio is derived from the XML representation, not from the internal representation. The <Values> node is represented as a semicolon separated list of parenthesis-enclosed rows, where each row in turn is a semicolon-separated list of parenthesis-enclosed columns, taken from the <ScalarOperator> nodes. This nesting of elements and the choice to use parentheses and semicolons for both levels make the list very hard to parse for a human reader.

However, the Values property in the full properties windows can be read more easily by clicking the + button; this opens a list where each <Row> node is listed individually, labeled [nn], where nn is the ordinal position of that <Row> element relative to the other <Row> elements. Each of those rows can then also be expanded, again by clicking the + button, to show each <ScalarOperator> as a node labeled [nn], where nn is the ordinal position of that < ScalarOperator > element relative to the other <ScalarOperator > elements within the same <Row> element. Note that SSMS orders both the <Row> elements and the <ScalarOperator> elements alphabetically instead of numerically, so if there are 10 or more elements, they are not listed in the correct order.

Because the graphical representation of the execution plan is derived from the XML representation, the Values property will be missing from the properties popup and the full properties window in the two cases (empty rows or zero rows) where the <Values> node is incorrectly missing from the execution plan XML.

Operator properties

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

Property name	Description
Estimated Number of Rows Per Execution *	Because the list of rows to be returned is stored in the Values property when the execution plan is generated, the Estimated Number of Rows Per Execution property is not actually an estimate; it gives the exact number of rows that will be returned in a single execution. The one known exception to this rule is when the Values property is empty (no rows to be returned), in which case the Estimated Number of Rows Per Execution property is still 1.
Values	This property lists the rows and columns that will be returned by the Constant Scan on consecutive calls. See the main text for details. The Values property is incorrectly missing from both the graphical execution plan and the execution plan XML in two cases: when a Constant Scan returns any number of empty (zero-column) rows, or when a Constant Scan returns zero rows. In these cases, it is not possible to find the number of rows a Constant Scan will return by looking at the Values property. In the first case (empty rows), that number of rows to be returned can still be taken from the Estimated Number of Rows Per Execution property. In the second case (no rows), even that fails because the Estimated Number of Rows Per Execution is set to 1 instead of 0.

Implicit properties

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

Property name	Description
Batch Mode enabled	The Constant Scan operator supports row mode execution only.
Blocking	The Constant Scan operator is non-blocking.
Memory requirement	The execution plan, and hence also its Values property, is stored in memory. Depending on the number of rows to be returned and the number and data type of those columns, the total size of the Values property, and hence the memory footprint of the execution plan, can be quite large. This is memory taken from the plan cache, though; there is no Memory Grant needed to store these values.
Order-preserving	The Constant Scan operator returns rows in the order in which they are stored in the execution plan. In theory, SQL Server could sort the data while compiling an execution plan to ensure the rows are returned in a specific order. However, as far as currently known, this is never actually done, so it appears as if the optimizer considers the output of a Constant Scan to be unordered.
Parallelism aware	The Constant Scan operator is not parallelism aware.
Segment aware	The Constant Scan operator is not segment aware.