We have been talking about creating an index on a view in order to enhance query performance. Today I wanted to share another technique that can be just as helpful.
Did you know you can create an index on a table or view having a where clause? The resulting index only contains records meeting the criteria of the where clause.
This can be helpful if you have a very large table where many of the records do not meet your criteria. If you make the index a covering index you are in essence creating a data mart with specific properties based on your where clause without having to create the mart yourself. Depending on the modification patterns of your data, this may be a sufficient implementation. If your data changes a lot it may not be the best technique.
Over the years I have seen many databases that perform a soft delete; rather than physically deleting a record a table may have a deleted Boolean column instead. When a user deletes a row the Deleted column is set to true instead of deleting the row.
Sometimes the majority of the rows in a table may be “Deleted”. In this case, a filtered index on the deleted column WHERE Deleted = 0 creates an index of only the non-deleted records, completely ignoring the deleted records.
Are you using filtered indexes? If so, what kind of problem was solved with your implementation? Leave your comment here, or drop an email to btaylor@sswug.org.
Cheers,
Ben