Filtered Indexes in sql server

A filtered index is a special index type where only a certain portion of the rows of the table are indexed.  Based on the filter criteria that is applied when the index is created only the remaining rows are indexed which can save on space, improve on query performance and reduce maintenance overhead as the index is much smaller. Filtered indexes are useful when we are creating indexes on tables where there are a lot of NULL values in certain columns or certain columns have a very low cardinality and we are frequently querying a low-frequency value. A filtered index only includes a subset of rows, it is smaller and more efficient to use, leading to faster query performance for queries that can leverage the index. It uses less disk space compared to a full non-clustered index.

Let’s see the example, suppose we have customer table in that we have email id only few customer have email ids. See the below table

create table dbo.customer ( id int identity(1,1) primary key not null, cus_name varchar(100), cus_add varchar(100), cus_email_id varchar(100))

 Let’s insert 100 records with email id.

declare @i int=1 while @i<=100 begin insert into customer(cus_name,cus_add,cus_email_id) select 'cust_name'+cast(@i as varchar(10)),                    'cus_add'+cast(@i as varchar(10)),                     'cus_email_id'+cast(@i as varchar(10))+'@gmail.com'                                 set @i=@i+1; end

 100 records inserted successfully. Now we are inserting 500k records with email id is null.

declare @i int=1 while @i<=500000 begin insert into customer(cus_name,cus_add) select 'cust_name'+cast(@i as varchar(10)),                    'cus_add'+cast(@i as varchar(10))                                   set @i=@i+1; end

500k records are inserted successfully.

Now in this table we don’t have index on search column.

select *  from customer where cus_email_id='cus_email_id1@gmail.com'

 Total Execution: 383 ms

Logic read: 3809 pages 

IO read: 2.81275  

 Let’s create filter index on the email column.

CREATE NONCLUSTERED INDEX IX_customer_cus_email_id ON customer(cus_email_id ASC) WHERE cus_email_id IS NOT NULL;

Filter index created successfully.

Now running the same query.

select *  from customer where cus_email_id='cus_email_id1@gmail.com'

Execution time: 70 ms

Logic Read: 5 pages

IO cost: .003125