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 make my living from training, other SQL related services and selling my book. Learn more at

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 is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via

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

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!Modern SQL logoA lot changed since SQL-92!

The Use The Index, Luke! mug

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

Shop now

“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