A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index
Syntax
CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria
Example
CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL
Advantages of Filtered Index
- Improved Performance: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records
- Lesser Maintenance Cost: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster.
- Lesser Storage: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index
Analysis
I have created a table patient with 1 lakh records of different Organisations and populated 70% data with OwnerOrganisation value 10 and selected the record with OwnerOrganisation value ="6"
Normal Index
CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID)
Filterer Index to exclude records of Organisation =10
CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID) WHERE OwnerOrganisationUID <>10
Conclusion
Index creation is always case to case basis as the need to create a filtered index should be carefully analysed based on the WHERE clause and the data distribution in the table. It is recommended to create filtered indexes if the data retrieved to be a smaller subset. Scenarios like columns with NULL data as major set and NOT NULL values of defined subsets could be a suitable candidate