Seek Predicate and Where Predicate in Query Plan

來源:互聯網
上載者:User
 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.

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.