 Hello, welcome to SS Unitec Associates site and this is continuation of SQL Server performance training. So today we want to discuss about table scans, cluster indexes scan and cluster indexing and what are the difference between these three. So let's start with table scan. So what is table scan? So when query performs a table scan it starts from the physical beginning of the table and goes through every record in the table. Like in our table we have 1000 records then if table scans will occur then it will goes all the records which is our label in the table it means 1000 records. So scan would be 1000 times. If row meets the condition then include into our result set and it would access if table doesn't have any index. So this is the table scan and this is bad for the performance. If we are getting all the data from the table then it may be good or may not be. Next is cluster index scan. So if table has a cluster index and it scales table as a cluster index. So let's assume you have a table and it contains many columns and you have created a cluster index on id column. So if id is not going to use in the where clause then that would be the cluster index scan. Query doesn't use non-cluster index or table doesn't have non-cluster index and it's bad in the performance until uses large data with the most of the columns and rows to be retrieved. And next is cluster indexing. So table with the cluster index scan is accessed and it uses that column in where clause. So it would scan as a cluster indexing. So it's better in the performance among these three. So go to our SQL server management studio to check about table scan, cluster index scan and cluster indexing. So as I am using SQL 2017 you may have other versions of SQL server. So here I am using adventure works 2k16. So here I just want to make the copy of adjustable. So let me execute this query to be created table temp. So this is adjustable and it shouldn't contain the constants like any cluster index, non-cluster index or any other. So we can check by using this SP help index. So we can execute as we can see the object adjustable doesn't have any indexes or you don't have permission. So we have permission but table doesn't have any constants right now. Now if I want to access data from this address temp table and we don't want to use any where clause then let me execute the query. So before that I just want to enable statistics IO. So we can execute set statistics IO on. So let me execute this query. So before going to execute let me enable execution plan. So now we can execute and it has around 19,614 records go to one execution plan and here as we can see it is using table scan. So this is bad for the performance as we go on messages tab. So here we can check the logical reads. So logical read as we can see 342. So now I just want to use where clause where city is Boston. Now we can execute this query and we can check logical reads. So before going to check the logical read go to one execution plan and here again we can see table scan go to one messages tab. So again it's going to scan all the pages which is 342. So this is bad for the performance because here we just want to get only one record but it is going to scan all the data and all the pages which is available in the table. Now let me create a cluster index on city column. So we can execute this create index. This time I just want to get all the records from this address tab table but here I am not going to use where clause. So let me execute and we can check. So as we can see it's having around 19,614 records go to one execution plan and here this time we can see cluster index scan. So if we have created any cluster index on the table and we don't want to use that cluster index then that execution plan will be referred as cluster index scan. So go to one messages tab and here we can check the logical reads. So logical read is 360 and before creating this cluster index as we can see 342. So why we are getting this difference because in case of cluster index it is going to create a tree and that tree could also contain some pages to arrange the data. That's why in case of cluster index scan it would going to return more logical reads. Now last is cluster indexing. So what is cluster indexing? Here as we have already created the index on the city column. So let me execute the query with city Boston. So we can execute and we can check. So here we are getting only one record go to one execution plan and here we can see cluster indexing. So cluster indexing is going to happen over here just because of we have used city column in where clause. So go to one messages tab and here we can see logical reads. So this time logical read is 4. So getting this data we just need to scan only 4 pages. So this is very best in the performance because as we have seen in case of table scan it was 342 then in case of cluster index scan it was 360 and now is only 4 logical reads. I hope you have understand about the table scan cluster index scan and cluster indexing and I hope you can create any cluster index on the where clause like in where clause we have 3 or more columns then we can create any cluster index among these columns. Then that would be cluster indexing and in case of cluster indexing it is going to scan less number of pages as compared to others. 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.