 Hello, welcome to SSC Vitek Sosily side and this is continuation of SQL Server performance tuning. So today we are going to discuss about the performance tuning with the like operator. So here if we are going to use the like operator in the where clause then what's going to happen? So here I have noted down few scenarios which will be discussed in this video. So first is like operator without wildcard character. In like operator with wildcard character we have 3 scenarios, first is wildcard characters are going to use at the starting of the string, second at the end of the string, third is at the starting and end of the string. So go to one SQL Server management studio to see in practical. So as I am using SQL 2017 you may have other versions of SQL Server. So here I just want to use the adventure works 2k16 database then here we just want to create a copy of person.address table. So we can execute this query. So table has been created successfully and 19,614 records should be inserted in the table. So let me execute set statistics Ion. Next is I just want to create a cluster index on the city column. So let me execute this query that is fine. Now in the first case without wildcard characters. So let me execute and check. So before going to execute let me enable execution plan and execute the query. So it's going to return only one record go to one execution plan. So here as we can see cluster index seek that is fine go to one messages tab. So here as we can see logical reads. So logical read is 4. So that is fine in the second scenario we just want to use the wildcard character at the starting of the string. So we can execute and we just want to check the output go to one execution plan. So here as we can see cluster index is canned. So this time seek is not going to happen because we have used wildcard character. So if we want to use the wildcard character at the starting then index seek wouldn't be happen. So go to one messages tab to check the logical reads. So logical reads is 360. So that is too much high as compared to first that was 4. In the second scenario let me execute if wildcard characters are used at the end of the string. We can execute and here we just want to check the execution plan. So in case of execution plan as we can see cluster index seek. So it's going to pick the right index go to one messages tab again we can see logical reads. So this time logical read is 3. So this is good for the performance. In the third scenario I just want to execute here we have used wildcard character at the starting and end of the string. So we can execute and go to one execution plan. So cluster index is canned. It means it's not going to pick the right index go to one messages tab to check the logical reads. So logical read is 360. That means it's too high. So as we can say that if we haven't used any wildcard character or we will use wildcard character at the end of the string then index seek will be happen otherwise if we will use the wildcard character at the starting of the string or starting and end of the string then index scan will be happen index seek wouldn't be happen. So we will take care while we want to use the like operator next is here we just want to drop the index which we have created that was cluster index and let me create a non cluster index on the city column. So index creator successfully and here we just want to check. So first we just want to execute this statement without using any wildcard characters go to one execution plan. So here this is our ID lookup as we have discussed about the ID lookup but here again we can see index seek it means it's going to pick the right index. Next is if we want to use the wildcard character at the starting of the string then go to one execution plan that is index scan go to one messages tab logical read is 90. So it's not going to pick the right index. In third case here we want to use the wildcard character at the end of the string. So let me execute go to one execution plan. So here again we can see index seek that is fine. Go to one messages tab that is logical read and it's three in the last case where we have used wildcard characters at the starting and end of the string. So let me execute and here we just want to check. So go to one execution plan and here again we can see index scan go to one messages tab. So logical read is 90. So as we can say that if we have used index that is cluster or non cluster so there is no difference between cluster and non cluster index in this case. So if we haven't used any wildcard character in the string or we will use the wildcard character at the end of the string then index seek will be happen. Otherwise if we will use wildcard character at the starting of the string or starting and end of the string then index scan will be happen. So please take care while we gonna use about the like operator in the where clause. So I hope you have understand how we can use it to get the better performance. 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.