 Hello, welcome to SSUnitech, so see this site and this is continuation of SQL Server Performance Training. So, today we are going to discuss about order of columns in non-prestate index. So, what it mean? So, let's assume we have a table and that would contain a non-prestate index and non-prestate index is based on three columns. So, if we will use only two columns in warehouse then what would be the output for the execution plan? If we will use only single column or we will use all the columns then what would be the output for execution plan? So, go to our SQL Server Managment Studio to see in practical. So, as I am using SQL 2.12 you may have another version of SQL Server. So, here I am going to use AdventureWorks 2K16 Database, so let me create a copy of person.address table. So, we can execute first statement and it would create the table and here as we can see 19,614 records have been inserted successfully in the table as well. Now, I just want to create a non-cluster index and this index will be based on address ID, city and postal code. So, first column is address ID, second is city then the postal code. So, let me execute this query. So, as we can see index created successfully. Now, so this is our first case here we just want to use all the columns like address ID, city and postal code. So, before going to execute this query let me enable execution plan. Now, execute this query. So, as we can see we are getting only one row that is fine go to an execution plan and here as we can see it is going to return index seed. So, it is good in performance. It means index is going to pick a right. In the second case as we can see we just want only two columns, first is city, second is postal code and in where clause we are going to use only address ID which is the first column in the non-cluster index. So, let me execute and check. So, go to an execution plan and here again it is going to use index seed. It means it is again going to pick the right index. It means if we will use all the columns or we will use only first column. So, both the cases index seed is going to be written by execution plan. Now, third condition this time as we can see we are going to use city which is the second column in the non-cluster index. Let me execute this query and here we just want to check execution plan. So here as we can see index is can. So if we will use only one column which is not the primary column it means first column in the non-cluster index then index seed will not be written by execution plan. Let me check for third column only postal code. Let me execute this query and going to check execution plan. So again we can see it is index is can. It means if we want to use only one column and index seek will be written then first column will be used in the where clause. Like in this case as we have created a non-cluster index with address ID city and postal code. So first column is address ID. So in where clause we have used address ID execution plan is going to written as index seek and rest the columns is going to written as index is can. So next is here as we can see we just want to use where address ID and city. So address ID is the first column and city is the second column. So let me execute and we want to check in execution plan. So again it is going to written as index seek. So going to check for first and last column which is address ID and postal code. So go to on execution plan again index seek. It means it is again going to pick the right index. It is here we want to use second and third column. So we can execute and we just want to check execution plan. So it is going to written as index is can. So these are the scenarios if we want to use the columns in the where clause then how we can use and execution plan will pick the right index. So if we have three columns then in case of first column it will going to pick the right index. In case of first and second column it is going to pick the right index. In case of first and last column it is going to pick the right index. But if we will use only second column or third column then it is not going to pick the right index as we are expecting. And if we will use only second column and third column in the where clause then index again not going to pick. So in these scenarios it will be written as index is can and rest of the scenarios will be going to written as index is. So I hope you have understand how we can create the non-prostate index according to our requirement and how we can use in the where clause to pick the right index. 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. Please say to others thank you so much.