SQL Server - Filtered Index

Summary on Filtered Index:

Filtered Index is used to improve performance of an query by applying filter on index thereby reducing maintenance and storage costs associated with index

Examples:

Create two Non-Clustered index one with filtered index and other without, as shown below:
 
CREATE NONCLUSTERED INDEX INDX ON Employee(EmpID) 


Creates an Non-Clustered Index on EmpID column within table Employee

CREATE NONCLUSTERED INDEX INDX2 ON Employee(EmpID)
WHERE Title = 'Manager'


Creates an Non-Clustered Index on EmpID column within table Employee with Filtered Index applied on Title column


Execution Results:


SELECT EmpID, Title FROM Employee WITH (INDEX(INDX))
WHERE Title = 'Manager'

 SELECT EmpID, Title FROM Employee WITH(INDEX(INDX2))
WHERE Title = 'Manager'

Drawing out an execution plan shows Query Cost associated with first select query is 54% and one with the second select query is 46% which depicts significant improvement using
filtered index