Dear Friends,
In my previous post of Simplifying SQL Server Execution Plan: Part 1 I’ve discussed on Clustered Index Scan. On this blog post we will cover following operators one by one;
- Clustered Index Seek
- Non-Clustered Index Seek
We are set to go;
Clustered Index Seek
Let us introduce a WHERE clause to the query that we used during clustered index scan demo;
Index seek is different from index scans as the optimizer is able to locate the index that it can use to retrieve intended records. It instructs query engine to look up the values based on the keys in given index. To re-iterate, in a clustered index not only structure is stored but also it sorts and stores actual data. You can imagine the way when we look up any specific word in the index of a book, when index seek takes place the key values are used to quickly identify row or rows of data. This is faster and efficient way of data retrieval compared to scans as actual data stored in indexes mentioned above.
In our example, we observe Clustered Index Seek against Person.Person table on PK_Person_BusinessEntityID which is the clustered key. When we look at the ToolTip window carefully, it is observed that Ordered option is set to True here which wasn’t the case during scans (discussed on my previous blog post).
Non-Clustered Index Seek
Let us run a query on Sales.SalesOrderDetail table first;
A non-clustered index seek also uses an index to look up desired rows like clustered index seek. Difference is, a non clustered index key is used perform search operation. Based on the query i.e. how they are written, optimizer will be able to find required data in non-clustered index or may route through clustered key. Here in our example, non clustered key IX_SalesOrderDetail_ProductID used to retrieve data set. We will cover more on this latter part of this series.
Let us stop here today; I will be back with next part soon.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
