by Markus Winand.

Distinguishing Access and Filter-Predicates

The SQL Server database uses three different methods for applying where clauses (predicates):

Access Predicate (“Seek Predicates”)

The access predicates express the start and stop conditions of the leaf node traversal.

Index Filter Predicate (“Predicates” or “where” for index operations)

Index filter predicates are applied during the leaf node traversal only. They do not contribute to the start and stop conditions and do not narrow the scanned range.

Table level filter predicate (“where” for table operations)

Predicates on columns which are not part of the index are evaluated on the table level. For that to happen, the database must load the row from the heap table first.

The following section explains how to identify filter predicates in SQL Server execution plans. It is based on the sample used to demonstrate the impact of index filter predicates in Chapter 3. The appendix has the full scripts to populate the table.

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL,
CREATE INDEX scale_slow ON scale_data(section, id1, id2)

The sample statement selects by SECTION and ID2:

SELECT count(*)
  FROM scale_data
 WHERE section = @sec
   AND id2 = @id2

In Graphical Execution Plans

The graphical execution plan hides the predicate information in a tooltip that is only shown when moving the mouse over the Index Seek operation. Hover over the Index Seek icon to see the predicate information—really, on this web-page.

The SQL Server's Seek Predicates correspond to Oracle's access predicates—they narrow the leaf node traversal. Filter predicates are just labeled Predicates in SQL Server's graphical execution plan.

In Tabular Execution Plans

Tabular execution plans have the predicate information in the same column in which the operations appear. It is therefore very easy to copy and past all the relevant information in one go.

DECLARE @sec numeric
DECLARE @id2 numeric
SELECT count(*)
  FROM scale_data
 WHERE section = @sec
   AND id2 = @id2

The execution plan is shown as a second result set in the results pane. The following is the StmtText column—with a little reformatting for better reading:

|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(...))
     |--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))
          |--Index Seek(OBJECT:([scale_data].[scale_slow]),
             SEEK: ([scale_data].[section]=[@sec])
                    ORDERED FORWARD

The SEEK label introduces access predicates, the WHERE label marks filter predicates.


About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

…to answer your current SQL questions.

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license