Filter Index in SQL Server 2008

SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.
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

How to select records with in Date Range

Selecting records within a given date range is one of the common requirement these days, but many people find it difficult. The most common mistake people make is always try to do the comparison as the same as the way we do for numbers as shown below

Wrong comparison
SELECT *
FROM Table
WHERE StartDate >= @P_StartDate
AND EndDate =< @P_EndDate

This wills not retrieve the qualified records as the comparison will not be against the range instead it will be against two dates and it will ignore any records fall within the range. The trick is to change the parameter to check the date in the reverse order as shown below.

Modified Query
SELECT *
FROM Table
WHERE EndDate > = @P_StartDate
AND StartDate =< @P_EndDate