Filter

Introduction

The Filter operator is used to remove rows from a result set that do not satisfy a specified condition.

Its most common usage is to implement a WHERE clause, although many elements of a WHERE clause get implemented in other ways (e.g. by pushing them down into an Index Seek or Index Scan operator), and Filter operators can also be used when the optimizer transforms other query elements into any type of filtering.

The Filter operator can only verify conditions based on the data in the current row; if data from different rows needs to be compared for the filter, the optimizer generates an execution plan that first adds the data to be compared to the row, or it uses different operators.

A special case of the Filter operator can test a condition that does not depend on the input rows, and completely bypass execution of its child operator if that condition does not evaluate to True.

Visual appearance in execution plans

Depending on the tool being used, a Filter 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 of the Filter operator depends on whether the value of the Startup Expression property is True or False. For ease of reading and understanding, I have created separate flowcharts for both cases, rather than trying to capture everything in a single flow chart.

This is the flowchart for a Filter property with Startup Expression set to False:

This is the flowchart for a Filter property with Startup Expression set to True:

Note that both these flowchart are simplifications. They don’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.

Evaluate predicate

When the Startup Expression property is False, the Filter operator applies the Predicate property to each row. This Predicate property holds an expression that may be as complex and as long as needed, but that always evaluates to a Boolean result. Since three-valued logic is used, that result can be True, False, or Unknown.

Only rows for which the Predicate evaluates to True are returned to the parent operator. When the Predicate evaluates to either False or Unknown, Filter discards the row.

Evaluate startup expression predicate

When the Startup Expression property is True, the Predicate property in the execution plan XML is surfaced in Management Studio and other tools as a Startup Expression Predicate property. Like the “normal” Predicate property, this Startup Expression Predicate is an expression that may be as complex and as long as needed, and that always evaluates to a Boolean result (True, False, or Unknown). However, when Startup Expression is True, the predicate cannot reference any data from the input of the Filter operator, since the expression is evaluated before even reading the first row.

If the Startup Expression Predicate property evaluates to True, the Filter operator reads all rows from its input and passes them unchanged. If it evaluates to False or Unknown, then none qualify. And since none qualify, producing them would be a wasted effort. So for that reason, Filter evaluates the Startup Expression Predicate property before requesting the first row, and immediately returns the end of data condition to its parent, without ever calling its child operator, if this expression does not evaluate to True.

Operator properties

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

Property name	Description
Predicate	The expression that the operator applies to the data in each input row to determine whether the row should be returned to the parent operator. Rows are only returned if this expression returns to True. If the Startup Expression property is set to True, then SQL Server Management Studio and some other tools display this property as the Startup Expression Predicate.
Startup Expression	If this property is set to False, the expression in the Predicate property is evaluated for each row from the input. This is the “normal” operation of a Filter operator. If this property is True, then the Predicate is evaluated only once, when the operator starts execution; and then either all input rows are returned unchanged, or the input is not executed at all. SQL Server Management Studio and some other tools relabel the Predicate property as Startup Expression Predicate in this case, for extra clarity.
Startup Expression Predicate	The expression that the operator applies when it starts executing, before requesting the first input row, to determine whether it will return any rows at all. If this expression does not evaluate to True, the operator does not request rows from its child but stops executing immediately. This property is not actually included in the execution plan. Tools such as Management Studio relabel the Predicate property as Startup Expression Predicate if the Startup Expression property is set to True.

Implicit properties

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

Property name	Description
Batch Mode enabled	The Filter operator supports both row mode and batch mode execution.
Blocking	The Filter operator is non-blocking.
Memory requirement	The Filter operator does not have any special memory requirement.
Order-preserving	The Filter operator is fully order-preserving.
Parallelism aware	The Filter operator is not parallelism aware.
Segment aware	The Filter operator is not segment aware.