 Hello, welcome to SS Unity Society site and this is continuation of SQL Server Performance Training. So, here we gonna discuss about Non-Cluster Index Scan and Non-Cluster Index Seq. So, in the last video of this video series, we have discussed about the Table Scan and Cluster Index Scan and Cluster Index Seq. So, if you haven't watched that video, so before going forward, I would strongly recommend to watch that video. So, let's start with Non-Cluster Index Scan and Seq. So, as we have discussed earlier in this video series, we cannot create more than one Cluster Index in a table. That's why we need to create Non-Cluster Index if required. So, what is Non-Cluster Index Scan? When query perform a Non-Cluster Index Scan, columns spot off Non-Cluster Index and access in the query. Let's assume we have a address table as we have seen in the last video. So, if we will create a Non-Cluster Index on the city column and in the select statement if we gonna select only city from the table, then that would be going to perform as Non-Cluster Index Scan and its bearing performance unless last data with the most of the columns and rows will be retrieved. Next is Non-Cluster Index Seq. So, what is Non-Cluster Index Seq? So, when query performs a Non-Cluster Index Seq, then columns spot off Non-Cluster Index and access in the query and it uses those columns in the where clause. Like, we have address table and we have created a Non-Cluster Index on the city column. Then in where clause, that city column should be used. And it's good in terms of performance. So, go to our SQL Server Management Studio to check in the practical. So, here I am using SQL 2.0.12, you may have Android versions of SQL Server. So, as we have created address temporary table in the last video, so let me drop this table. So, table dropped successfully. Now, let me create the table and it would going to take the difference from address table. So, let me execute the query. So, as we can see 19,640 and record should be inserted with the table. So, as we can check the if we have any index or not. So, initially we have discussed if we want to create any table with the into command then it would going to create the table and insert record on the table but it will not going to create any constant. So, we can check by executing sp help index then the table name. So, here as we can see the object address time doesn't have any index right now. Before going to test the table scan. So, let me enable this set statistics I own. So, we can execute and now we can execute this address time table and here we can get the logical reads. So, we can execute and go to on the messages tab. Here we can see logical read is 342. So, 342 is the logical read as we seen in the last video as well. And, if we will use the where clause where city is Boston then again we can see it will going to return 342 records because it will scan all the pages which is available for the table. Now, let me create a non cluster index this time with the city column. So, we can execute this command and it should be created. Now, go to on the non cluster index scan. So, this time I just want to get the city from this address time table. So, we can execute and we can check. So, before going to execute let me enable the execution plan. Now, execute the query and go to on the execution plan and here we can see index scan and it is non cluster index as we can see. So, go to on messages tab and here as we can see logical reads. So, logical reads is 89. So, if we want to get data from this non cluster index scan and it would read only 89 pages to get this city column. So, let me check about non cluster index seek. So, here we just want to use this city column as in where clause. So, we can execute this query and here we are getting only Boston that is fine. Go to on execution plan and this time we can see index seek that is non cluster index. Now, go to on messages tab to check the logical reads. So, this time logical read is 2. So, it would take only 2 pages to get the output. So, this is non cluster index seek. If we will compare between these 3 then in case of table scan it would take around 342 pages to get the output and in case of non cluster index scan it would take 89 pages to get the output and once we going to use the city in where clause then that would be the non cluster index seek. It would take only 2 logical reads to get the output. It means only 2 pages to be scanned. So, here as we can see we are getting only 1 column that is city. But our requirement we want all the columns from this address time table. So, let me execute this query and check the execution plan. So, we can execute and go to on execution plan and here as we can see RID lookup. So, we will discuss about RID lookup in the next video. So, here basically it would take additional lookup with the table because in non cluster index we have only 1 column that is city and other columns are available in the table. That's why this RID lookup is required. So, we will discuss about this RID lookup in the next video in detail. So, I hope you have understand about the non cluster indexes scan and non cluster indexing. So, thank you so much for watching this video. 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.