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.

On my Own Behalf

I offer training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

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 provides insights into SQL and shows how different systems support it at Previously he made, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via

Buy his Book

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

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“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 and GDPR | CC-BY-NC-ND 3.0 license