 Hello, welcome to SSRuitech Sushilisite and this is continuation of SQL Server performance journey. So today we gonna discuss about covering query and this is the solution of RID and key lookups. So if you haven't watched part 15 of this video series, so I would strongly recommend to watch that video where you can understand about the RID and key lookups. So link of this video will be available on the description of this video. So let's start with covering query. So what is covering query? So covering query one which can satisfy all requested columns in the query without performing a further lookup into the table as we seen in the last video. Like we have address table and in address table we have created a non-plaster index or city column and we want to get all the columns which is available on the table with the city. So it's required a additional lookup with the table to get other columns except city. So this is the issue with the RID lookup and key lookups as we have discussed in the last video. Here we can add in include in non-plaster index because those column will not be a key column for the index. So we can use the include those columns which we want to use in the selected statement and that would be the covering query. So cluster index will be our basic covering query. So as we can see over here, so this is the structure for non-plaster index. It's non-plaster index and below is cluster index. If we want to get data from the non-plaster index, if all the data is available on the non-plaster index, then there is no need to go to on the table which is our cluster index to get the data. So go to on Secoso management studio to see in practical. So how we can create the covering query and how we can reduce the performance. So here, so let me drop this table because in last video we have created this table. So we can execute and table has been dropped. Now let me execute this statement to create this table again. So here I just want to create a non-plaster index with the city and postal code. So we can execute and it's going to create a non-plaster index. That is fine. Now we can execute and this time I just want to get address ID, address line 1, address line 2, city and postal code. Because as we know city and postal code is available on the non-plaster index and other address ID and address line 1, address line 2 is available on the table only. So it would going to use a additional lookup that is our ID lookup because table doesn't have any cluster index right now. So before going to execute this query, let me enable the execution plan and now I want to execute this query. So here we are having only one record that is fine. So we have got one execution plan. In execution plan as we can see our ID lookup and it's going to take 89% of the cost. That is very high cost. So now how we can avoid this problem. So let me drop this index right now and this time I just want to create non-plaster index and in the include list as we can see this is the include list with the address ID, address line 1, address line 2. And here our key column that is city and postal code. So here this is very important because these two will be performed as an index but these three columns will not be part of the index but it would going to store the data on the non-plaster index. So let me execute. So index has been created successfully. Now I just want to execute this query to verify. So here now go to on execution plan and this time as we can see it's going to perform as indexing. And previously as we seen it's going to perform a additional lookup that is our ID lookup and that was taking 89% of the cost. That was very high cost. So we can use the include keyword and we can simply add those columns which is going to perform in the selected statement and not in the part of non-plaster index. So I hope you have understand about the covering query. So how we can avoid our ID lookup and bookmark lookup by using this covering query. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.