Filtered Indexes

I got an idea to revisit the ability to optimize a LIKE query. These queries really can’t be optimized if the % is in front of the keyword, such as WHERE col LIKE ‘%Manager’ (to get the various types of managers), because the optimizer will skip the index and scan the column, but, I wanted to check things out since I haven’t gone back and looked at this in either SQL Server 2005 or SQL Server 2008.  Note that my first attempt at this was to check to see if an old Oracle trick could be implemented, which is to reverse the keyword using reverse, and reverse the where clause so that the wildcard was after the search keyword, and not before it, such as the following:

SELECT CustId
FROM Customers2
WHERE REVERSE (ContactTitle) LIKE REVERSE('%Manager')

This does not work in SQL Server because there is nothing in the optimizer to assist with this query. Oracle has a REVERSE keyword as an option when creating an index that they claim will assist here. I’ve known about this in Oracle for a long time, but have never tested it to speak of, assuming that it still works with the newest versions.  I searched on Google! for some to see what is out there for optimizing the LIKE keyword, but didn’t find anything. So, a query such as this would still be best suited for full-text indexing for the most part.

Filtered Indexes might be something to think about when performance tuning, but I find that the technology is very raw and not terribly flexible.  One main stipulation is that from Books online… “predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.”  Simple logic is =, >, <, etc. is ok, but I find that in order to make this usable, I seemed to pick operators that were not a part of its’ functionality.  The computed column stipulation would also be nice to have, since for this example you could create a computed column on RIGHT(ContactTitle, 7) to be = ‘Manager’, which you could then index.  (Yes, you get an extra column and some I/O to calculate it, but not a terrible amount of processing power to have the column).  A Filtered Index WHERE clause does allow an IN predicate, however this doesn’t deliver much of an advantage over a standard nonclustered index other than losing a few logical reads.

Bottom line: Check out this feature when creating indexes, and keep it in mind when developing and performance tuning. However, based on my testing, I am not thoroughly impressed with this option in the initial release of functionality and features should be added in the next iteration.

Lee

---------------------------------

Indexes? We don’t need no stinkin’ Indexes!