Filtered Index in MS SQL

What is filtered index? What is the benefit of using filtered index? Does filtered index really improves the performance? All these queries will be answered in this article.
Lets start with what is filtered index? A kind of interview question.
Filtered index is an optimized non clustered index. Filtered index is more suitable for the queries which select the data from a well defined subset. Important thing you have to remember about the filtered index is it uses the filter predicate to index only a portion of rows in a table. This is what I have mentioned above that “filtered index is suitable for the queries which select the data from a well defined subset”.
Does the filtered index improve the performance?

Of course yes, but it can degrade your performance too if the filtered index is not well designed. A well designed filtered index definitely improves the performance. No doubt on that, otherwise Microsoft will not spent money on it! The reason why I say filtered index gives better performance than non-clustered index is filtered index covers only the rows associated with the filtered index. If you are confused with this statement you will be more clear when you see the sample.
Another advantage of using filtered index is that the maintenance cost of the index is less compared to the full table non-clustered index. Again the reason for reduced maintenance cost is because filtered index is very small compared to the full table non-clustered index and is only maintained when the data in the index is changed. Another important thing is filtered index is maintained only when the DDL query affects the data in the index.
Filtered index also reduces the index storage cost. As you already understood from the above that filtered index reduce the disk storage so what you can do is rather than creating single full table non-clustered index you may create multiple filtered index.
Now lets see how we create the filtered index.
Lets create a table first.

CREATE TABLE [dbo].[tblStatistics](
    [ID] [numeric](18, 0) NULL,
    [Name] [varchar](50) NULL,
    [Salary] [numeric](18, 0) NULL,
    [City] [nchar](20) NULL
) ON [PRIMARY]

Lets query the data in this table.

Now I am going to create filtered index.

CREATE NONCLUSTERED INDEX FICity
    ON [tblStatistics] (ID, City)
    WHERE City IS NOT NULL ;
GO

It’s time to create a simple query which utilize the filtered index we have created above.

SELECT [ID]
      ,[Name]
      ,[Salary]
      ,[City]
  FROM [DemoAzure].[dbo].[tblStatistics]
  where city is null and Salary>8000

To know whether the query used the filtered index or not, you may display the query execution plan. Predicate

I hope now you are familiar with filtered index and you may start using while developing the application to improve the query performance.
Thanks for reading my article. Please provide you feedback in the comment section.