 Hello! Welcome to SS-Initech. So, see this side and this is continue lesson of SQL Server Performance Training. So, today we are going to discuss about RID lookup and key lookups. So, what are these two lookups? So, in the last two videos we have discussed about the table scan, cluster indexes scan, cluster indexing, non-cluster indexes scan and non-cluster indexing. So, if you haven't watched these two videos, I would strongly recommend to watch these two videos before going forward. So, let's start with RID lookup and key lookup. So, first is what is lookups? So, if all the requested column by the query is not available in the non-cluster index itself, a lookup is required to the base table to pick up those columns which are not part of the non-cluster index being used to retrieve the data. So, what it mean? So, let's assume you have an address table as we have used in the last two videos and here we have created a non-cluster index on CT column and we want to get all the data from the address table where CT is Boston or anything else. So, CT columns data is available on the non-cluster index. So, it will be going to pick from the non-cluster index but other columns data is not the part of non-cluster index. So, it was required to additional lookup with the base table to get the other columns data. So, here we have two type of lookups. First is RID lookup, second is key or bookmark lookup. So, what are these two lookups? So, first is RID lookup. So, what is RID lookup? So, if base table doesn't have any cluster index on it, a row ID is heap will be used as a row allocator and if query is required to lookup using the heap row ID then it will be performed as RID lookup. Next is key lookup. So, what is key lookup? If base table has a cluster index on the table and non-cluster index is also created on the same table and at this time it will have cluster key as row allocator. If any query required lookup using this cluster index key locator will be performed lookup and it's called a key lookup. Basically, we can understand if our table has a cluster index then that would be performed as key lookup. If table doesn't have any cluster index then that would be performed as RID lookup. So, go to our SQL server management studio to see in practical. So, here I am using SQL 2017 and here I am using Adventureworks 2k16 database. So, in last video we have created this at the stem table. So, let me drop this table and now let me create the table. So, table has been created successfully. Now, as we know it doesn't have any constants right now as we have seen in the last videos. So, let me create a non-cluster index with the city and postal code. So, we can execute non-cluster index is created successfully. Now, I just want to check city and postal code. So, before going to execute this query let me enable include execution plan. So, now we can execute the query and go to one execution plan as we can see it's going to use non-cluster index with the non-cluster because city and postal code both are available in the non-cluster as we can see city and postal code. If we want to get all the columns which is available on the address table so, we can execute and this time we don't have other columns like address line 1, address line 2, address ID and many more columns. And non-cluster index has only city and postal code. Now, we can check the execution plan. So, this time as we can see it's going to use a nested loop join. So, we will discuss later on this video series about the nested loop joins. So, this is the physical join as of now you can understand. So, here as we can see this is indexing and it's going to get data only for two columns that is city and postal code. And it's going to use a listener lookup because our table doesn't have any cluster index right now. That's why RID lookup is required as we can see inside this RID lookup that is heap. If table doesn't have any cluster index then it's going to use as a RID lookup. Now, let me create a cluster index on this table and this time I just want to create a cluster index on address ID column. So, we can execute this query to create cluster index. So, cluster index is created successfully. Now, I just want to get all the columns from this address table where city is Boston. So, we can execute this query and now go to an execution plan and this time as we can see this is going to use as key lookup that is clustered because our table has a cluster index. So, as we can see here it's going to use the key lookup but in terms of performance as we can see non cluster index seek is going to use only 11% of the cost with the batch. But in case of key lookup it is required 89% of the cost that is very costly. So, if we will face any scenario like this then we can create a covering index. So, we will discuss covering index in the next video. So, I hope you have understand about the key lookup and RID lookup. So, in the next video we will discuss about the covering index. So, thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos and please share to others. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.