TSQL Execution Plan Operator - Index Seek
In SQL Server we have 2 types of operators for seeks and the first kind we’ll learn is the clustered index seek, and that means that we’re retrieving rows based on a seek predicate, so it’s a sargable predicate from a clustered index.
Another one is a nonclustered index seek and that’s the same as the clustered index seek where we’re retrieving rows based on the seek predicate. But in this case, we’re doing that against a nonclustered index. In this video, we’ll be going to learn about these two operators.
In this session, we’ll be going to learn about one of the T-SQL execution plan operators called Index Seek. In SQL Server we have two types of operators for seek. The first kind we will discuss is the Clustered Index Seek. The Clustered Index Seek means the SQL server retrieving rows based on a seek predicate. It is a sargable predicate from a Cluster Index. The word sargable is a contraction of Search Argument Able. The optimizer’s ability to benefit from an index depends on the selectivity of the search condition, which in turn depends on the selectivity of the columns differing in the ‘where close’, ‘on close’, or ‘having close’. All of which are referred to as the statistics on the index. In contrast, we also have a non Clustered Index Seek that is the same as a clustered index seek where we are retrieving rows based on the seek predicate. But in this case, we are doing that against a non-cluster index. The search predicate used on the columns in the where close determines whether an index operation on the column can be performed or not. Let’s perform the demo (Patient Master Table) for this operator.
In the previous session, we have already created the Cluster Index using this {CREATE UNIQUE CLUSTERED INDEX PatientMaster_PatientId ON [dbo]. PatientMaster} script on the patient master table. Before executing the script, enable the execution plan. When I executed this query, I got one row for this patient ID. When I go to the execution plan tab, we can see that we have an index seek operator. Here the patient ID has the index and that is our clustered index. Now, when I hover over the index seek operator, you will see the seek predicate. In the seek predicate we can see the condition, Patient ID equals a specific value.
Let’s go ahead and perform the Nonclustered index demo. I am retrieving the patient records based on the last name where the last name is equal to John. When I executed this query, we got two records from the table.
But when I see the execution plan, you can see that here I have a clustered index scan operator. So to get rid of this index scan, I have to create the nonclustered index on the last name column. Let me create a nonclustered index. When I execute the same query again and in the execution plan here, you can see the index seek. When I hover over this index seek operator, you can see our newly created nonclustered index in the object section.
For the previous query, it was a clustered index and it had an index seek. Now we have a nonclustered index and it has an index seek. Index seek can happen at either level, at the cluster index level, and at the non-cluster index level. In this query, what I said, the last name was equal to John, then the SQL server engine started the evolution process as B tree traversal for the non-cluster index and retrieved the record for us.
For more videos and articles visit:
Useful Links:
👉 GET YOUR FREE API KEY for PDF.co
https://app.pdf.co/signup?utm_source=youtube
✅ ON-PREMISE SDK FREE TRIAL:
https://bytescout.com/download/web-installer?utm_source=youtube
🔎 SDK DOCUMENTATION:
https://bytescout.com/documentation/
TSQL Execution Plan Operator - Index Scan 