 Hello, welcome to SSRitech, Susie Lee site and this is continuation of SQL Server performance training. So today we gonna discuss about filtered index. So what is filtered index and how we can improve the performance by using filtered index. So let's start with filtered index. So filtered index was introduced in SQL Server 2008 and a filtered index is nothing but non cluster index along with aware condition. Let's assume we have a table with 100,000 records but we just want to create a non cluster index only 20,000 records so filtered index help us. Due to aware condition indexing will be done only portion of the records. Next is it will significantly improve the query performance because it's smaller than the non cluster index. Once we will create a non cluster index then it will be created on the full table but here we just want to create a non cluster index only a portion of the records. Next is the maintenance cost of the filtered index is less because it's used smaller part of the index. Next is it will reduce the disk storage space. As we have discussed when we would have discussed about the non cluster index non cluster index will take an additional space. Next is it can be used only with non cluster index so this is the disadvantage of the filtered index. So go to on SQL Server Management Studio to see in practical. So here I am using SQL 2017 you may have another version of SQL Server. So here I just want to create three tables first for without any index, second for full index and then for filtered index. So let me execute this script to create three tables. As we can see these three tables has only two columns first is ID second is name. Now I just want to insert record on these three tables. So here I just want to insert 100,000 records on each table. So let me execute this script to insert the records. Data have been inserted successfully on the tables. So here I just want to create a normal non cluster index on the ID column in EMP index table. So let me execute this script to create non cluster index. So command completed successfully it means index has been created. So this is for filtered index. So here as you can see first two lines. So this is normal non cluster index and here we have additional one line which is wear condition. So once we will implement wear condition with the adjusting index then it would be filtered index. So let me execute this query. So here we just want to create index only 10000 records on the table. So let me execute and index has been created successfully only 10000 records on this table. So now as we can see first is without any index second is with full index and third is with filtered index. So let me execute this query to check the performance. So before going to execute this query let me enable execution plan. Now we can execute. So go to one execution plan. So here as we can see for no any index 89% of the cost. So this is the related batch of the cost. Next is 7% once we have created non cluster index. Third is non cluster index with wear clause that is filtered index. So it's gonna take only 4% cost of the related to batch. So as you can see this is the best in the performance among these three. If we don't have any index then 89% if we have normal index then 7% if we have filtered index then we have only 4% of the cost. So I hope you have understand how we can use filtered index according to our requirement and how it can be used to improve the performance as well. 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 share and subscribe our channel. Thank you so much.