 Hello, welcome to SSUnitech. So, since this side and this is part 5 of SQL-Sogo-Prohomance training. So, today we want to discuss about the indexes and cluster index. So, what is indexes? So, go to our next slide to understand about the indexes. So, what is indexes? So, the principal purpose of an index is to speed up our data retrieval. Like we have a selected statement and we are getting the data biologic selected statement and it is going to take a lot of time. The purpose of index to narrow down the time. Next is indexes are created on the tables and views. Next, if table doesn't have any index then query engine checks every record from the beginning to end and it's called table scan. And table scan is bad for the performance. If we have a lot of data. Next, if table has an index that can be increased the performance of the query. This is the basic purpose of the index and let's move to next slide. So, here as you can see types of indexes. So, first we have cluster index then non-cluster index. So, these two types of indexes mainly used in the OLTP server. So, OLTP means online transaction processing. Then, unique indexes. Then, covering index. Then, filter index. Then, has index. Then, column store index. So, mainly column store index is used for OLTP server that is online analytics processing. Then, spatical indexes. Then, full text indexes and XML indexes. So, we have total 10 types of indexes in SQL server and here we will discuss about the cluster index. So, go to our next slide to understand about the cluster index. So, it is determined to the physical ordering of the data in the table based on the cluster index column. That's why a table can have only one cluster index because if we will create a cluster index on a table. Table is arranged according to that cluster index and if we will try to create one more cluster index then our table will be resupported and table can't be resupported because it already has a cluster index. That's why our table will contain only one cluster index. Then, once we create the cluster index into the table then it sort data into B3 according to the cluster key column. So, here what is the syntax of the cluster index. Simply, we need to write create cluster then the index name on the table name. Then, we need to specify the columns by which we want to create the cluster index. Here, I have only one column but you may have one or more than one columns and we can also set ascending and descending according to our requirement. So, go to our next slide. So, here as we can see we are having these two images first for the ascending and second for the descending. So, if we will create any index with this column let's assume this column is ID and we want to create a cluster index on this ID. So, before that data is stored in the heap then our data will be resupported and in the first image if we will create cluster index in ascending order then the right side value will be always greater than the root value. In this case, root is 4 then the left side value will always be less than 4 and right side values will always be more than 4 and if we will create cluster index with the decreasing order then order will be like this. Left side values will be always more than 4. So, as we can see 7, 8, 6 and 5. So, these values are more than the 4 and right side values will always be less than the 4 as we can see 2, 1, 3 and 0. So, this is the basic idea about the bit tree. Here I just want to create a cluster index for the testing purpose. So, this is the table and I want to create one more table and it will use the address that is fine then we can say that tmp. Now, let me execute this query. So, it will load the data and structure but it will not going to load the constants. So, if we want then we can check we can simply press alt F1 and here it will show the information about the table. So, here as we can see we do not have any constants. So, we have only identity but we do not have any cluster index or non-cluster index. So, let me create a cluster index on this table. So, we can press select as tick from this table and it has a lot of data and I just want to create a cluster index on this address id column then we can write create cluster index then the index name mainly we will start with ix and then the table name then the column name that is address id then on again table name then we need to specify the column name. So, we want to create on address id let me execute this query. So, index is created successfully. Now, if I want to create index one more time on this table. So, this time I just want to create in city and that would be again cluster index. So, let me try we will not able to create one more cluster index. So, we can check. So, cannot create more than one cluster index on this table. If you want then we can drop our existing and we can create anyone. 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.