Filtered Indexes in SQL Server

Filtered Indexes in SQL Server

Filtered Indexes in SQL Server

Overview

In this post, I will explain all about Filtered Indexes in Microsoft SQL Server end to end along with appropriate examples. So let’s start –

Filtered Indexes in SQL Server

Filtered Index: A filtered index is a Non Clustered Index with a predicate that allows us to specify which rows should be added to the index.

Syntax:

CREATE INDEX index_name

ON table_name (column_list)

WHERE PREDICATE

In above syntax we have to specify the name of the filtered index after the CREATE INDEX clause then the table name with the list of key columns that will be included in the index and then use a WHERE clause with a PREDICATE to specify which rows of the table should be included in the index.

Why Filtered Index?

As we have seen previous examples that a Non-Clustered Index can greatly improve the performance of queries, benefits of Non Clustered Indexes come at costs, storage and maintenance because it takes additional storage to store the copy of data of the index key columns and when we INSERT, UPDATE or DELETE rows from the table then Microsoft SQL Server needs to update the associated Non-Clustered Index. It would be inefficient if any application just query a portion of rows of a table, due to this filtered index has come into play.

Benefits of Filtered Indexes

A filtered index can help us especially when the index key columns have many NULL values, when any index key column has many NULL values then they called sparse. Filtered indexes reduce the maintenance cost because only a portion of data rows, not all need to be updated when the data in the associated table changes.

Now we will go through some examples, in this way we will take a table Students for example which has few columns like – ID, Name, Mobile, Address, City, State, and PinCode, in this table in Mobile and Address column contains many NULL values, as we can see by using this select query below –

Query:

SELECT SUM(CASE WHEN Mobile IS NULL THEN 1 ELSE 0 END) AS [Has Mobile],
SUM(CASE WHEN Mobile IS NULL THEN 0 ELSE 1 END) AS [No Mobile]
FROM dbo.Students

Result:

Has Mobile | No Mobile
2 3

As we can see from the above result that column Mobile is good for the filtered index. Now we will create a filtered index for the Mobile column of the dbo.Students table using below syntax –

Syntax:

CREATE INDEX ix_Students_Mobile
ON dbo.Students(Mobile)
WHERE Mobile IS NOT NULL

Now we will try to find the Student whose mobile number is 9956267687 using below query –

Query:

SELECT Name, Mobile
FROM dbo.Students
WHERE Mobile = 9956267687

We can improve the key lookup by using an index with included columns which include the name of the students in the index –

Syntax:

CREATE INDEX ix_Stud_Mobile
ON dbo.Students(Mobile)
INCLUDE (Name)
WHERE Mobile IS NOT NULL

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply