Seek Predicate and Where Predicate in Query Plan

最新推荐文章于 2022-11-21 17:47:03 发布

转载最新推荐文章于 2022-11-21 17:47:03 发布 · 238 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xingyukun/archive/2008/05/17/1201144.html

本文深入探讨了SQL Server查询计划中Seek Predicate与Where Predicate的区别。Seek Predicate用于索引遍历，定位到可能满足条件的记录；Where Predicate则进一步筛选这些记录，确保完全符合查询条件。文章通过实验验证了Seek Predicate倾向于使用聚集索引的前几列。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

When we look into a query plan, in a Seek Operation Node, we could can find 2 Properties: Predicate and Seek Predicate.Before I was really not clear about them, and today I read the bellow info:

SQL Server 2005 Books Online (BOL) explains the difference between the Seek Predicates and the WHERE Predicate: “The storage engine uses the index to process only those rows that satisfy the SEEK:() predicate. It optionally may include a WHERE:() predicate, which the storage engine will evaluate against all rows that satisfy the SEEK:() predicate (it does not use the indexes to do this).” In other words, SQL Server uses the Seek Predicates to traverse the index B-Tree from root to leaf level and to perform the partial ordered scan at the leaf level to find matches, then evaluates the WHERE Predicate against all matching rows that the Seek Predicates processed.

From above, we can guess that Seek Predicate should use the first columns of the Clustered Index, which are big guys who define the structure of index B-Tree,and Predicate is what you put in your where clause except the ones which has been included in Seek Predicate. Then I did an experiment here:

select * from TableA
where [col1]=-1216403245980715165
AND [col2]=0
and [col3]=50004
and [col4]=1
AND [col5]>=14003
AND [col5]<=14003
and [col6]=1

Then Seek Predicate is the first 3 columns in the Primary Key:
[col1] ASC, [col3] ASC, [col4] ASC

So my guess is confirmed.

转载于:https://www.cnblogs.com/xingyukun/archive/2008/05/17/1201144.html