 Hi there, this is Krishnan from Bytescout and in this video, we will be going to learn about one of the T-SQL execution plan operator, Lookup. To maximize the benefit from the non-clustered indexes, you must minimize the cost of the data retrieval as much as possible. A major overhead associated with the non-clustered indexes is the cost of excessive lookups. Lookup operator manifests in a couple of different forms. The first is the key lookup operator, which formerly known as bookmark lookup, which are a mechanism to navigate from a non-clustered index row to the corresponding data row in the clustered index, and it's always through a nested loop operation. A key lookup occurs when data is found in a non-clustered index, but additional data is needed from the clustered index to satisfy the query, and therefore a lookup occurs. And we can avoid them with a covering index. We will see this in the upcoming demo. And then another form of lookup is RID lookup. This RID lookup is associated with a bookmark lookup to a heap. So when your table doesn't have a clustered index, optimizer uses RID lookup instead of key lookup using the clustered index. Alright, so for the demo purpose, here I am using an adventure work database, and the first thing I want to do is to execute this query. So in this query, we are accessing data where the last name is equal to Russell, and in my select close, I have a business entity ID, first name, last name, and patient type. But before executing this query, let me enable the execution plan. And now let's execute this query. Now if we look at the execution plan, we can see that we have an index sick using this index ix underscore last name. But we also have a key lookup operator on the clustered index of the person table. And we also have a nested loop operator right here. And the reason why we have that lookup is because that particular index ix underscore last name, even though it's it's being used to traverse the B tree and get the qualifying rows, but it doesn't have all the columns that we need for the final result, such as first name and person type, and so on. So to get rid of this lookup, we need to make this existing index into a covering index. Well there are pros and cons in doing this, but in this scenario, if we want to cover the query entirely, we can recreate this non-clustered index. So in our case, in this index, I have added first name and person type in this include section. Now before executing this non-clustered index, one thing we need to note here is the estimated subtree cost of this entire select statement. So when I hover over this operator, we can see our estimated subtree cost is 0.408202. And now let's go ahead and recreate this index with a covering column. And now let's execute this same query again and when I go to the execution plan. So here we can see that we no longer have a key lookup and we also no longer have the nested loop operator. And it is using our updated index Ix underscore last name because this index has everything that it needs. It has the key column and it has the lookup column in the leaf level. Now once again, let's take a look at the estimated subtree cost and this time you can see that it is 0.0034206, which is very lesser compared to the previous subtree cost. Alright now stay tuned with us as in the next video we will be going to learn about our next operator called nested loops.