SQL SERVER Predicates – Seek & Residual Predicates

Today let’s talk about predicates. Predicates can be defined as an expression that evaluates to TRUE, FALSE, or NULL (UNKNOWN). They are also called as conditional expressions.

Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, Case_N function and other constructs where a Boolean value is required.

Example of a Predicate-

--

SELECT ID,Name,City FROM testPredicates
WHERE ID = 5

--

Predicate in a WHERE clause specifying a condition for selecting rows from the Area table. Simple predicates use one of the operators =, , >, >=, <, <=, IN, BETWEEN, LIKE, IS NULL or IS NOT NULL.

Predicates can be enclosed in parentheses if desired. The keywords AND and OR can be used to combine two predicates into a new one. If multiple combinations are applied, parentheses can be used to group combinations to indicate the order of evaluation. Without parentheses, the AND operator has a stronger binding than OR.

Well I will not go into the details of operators. You can find a detailed information @ https://en.wikipedia.org/wiki/Where_(SQL).

Well there are two kinds of predicates-

• Seek Predicate
• Residual Predicate

Example of Seek Predicate

--

SELECT ID,Name,City FROM testPredicates
WHERE ID = 5

--

If we check the execution plan of the above query we will get a Clustered index seek and that we have a seek predicate. Check out the picture below-

Now let’s check one more query.

--

SELECT ID,Name,City FROM testPredicates
WHERE Name = 'Pawan' 

--

In this case also we are getting NonClustered Index Seek and a seek predicate. Check the picture below-

Example of Residual Predicate

--

SELECT ID,Name,City FROM testPredicates
WHERE Name = 'Pawan' AND City LIKE 'Pune%'

--

In residual predicates the optimizer has to do some extra work to fetch the required rows. In this case we have an index on two columns, we can only use the index to satisfy a predicate on the first column as we have an equality predicate on the first column. Even if we can use the index to satisfy the predicate on the second column, we may be able to use it on the first column. In this case, SQL Server uses residual predicate for the predicate on the second column. This is the same residual predicate that we use for scans.

A residual predicate is one that has an hidden extra cost because the predicate has to be tested again on every combination of rows that is fetched.

I hope you have enjoyed the article. Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com