This week we're going to take a look at issues with filtered indexes. This feature was a very welcome addition in SQL Server 2008 and has enjoyed widespread usage if conversations on twitter and forums are any indication. But their implementation is not perfect – bugs in SSMS and numerous restrictions on their use have certainly reduced their impact, or at least made much more work for folks using them.

SSMS failures

Several people have reported various cases where Management Studio trips over filtered index functionality. I would only expect these bugs to be fixed in either 2008 R2 SSMS or Denali (in some cases both) – don't hold out for your 2005 or even 2008 versions of SSMS to be fixed for filtered index or other "new feature" issues.

#526911 : Table designer fails to correctly script filtered index

#462053 : The filter expression of a filtered index is lost when a table is modified by the Table Designer

#362699 : Table designer doesn't script WHERE clause in filtered indexes
 
#330238 : There is no way of creating filtered index from SSMS while designing a new table

#521333 : View Dependencies fails with Transact-SQL Error 515

Engine failures

Well, there is only one of these outstanding at this time, at least that I could find. I suspect this has to do with the testing matrix – they added both filtered indexes and MERGE in SQL Server 2008, but did not thoroughly test them *together.* There is a discussion thread on the MSDN forums about this specific issue:

#596086 : MERGE statement bug when INSERT/DELETE used and filtered index

Missing functionality

Some of these items are categorized as bugs, but I believe they simply represent scope creep. One of the items actually says something along the lines of, "It's not a bug; it's a known gap in functionality." Personally I think they got the bare minimum into SQL Server 2008 (supporting unique constraints the ANSI standard way, where more than one row can be NULL), and had no intention of supporting all of the other things that could make filtered indexes even more useful. If you find any of these suggestions valuable, please comment and vote – hopefully we can convince them to invest some time into this feature area for the next version after Denali!

#454744 : Filtered index not used and key lookup with no output

#341891 : Filtered index creation failed with NOT IN clause

#341737 : Filtered index does not allow filters on disjunctions

#648551 : filtered index on indexed view

#666238 : Support for more complex WHERE clause in filtered indexes

#518328 : Should be possible to create a filtered index on a deterministic persisted computed column

#498009 : Allow filtered unique index to be a canditate key for a foreign key

#329805 : Enhancements to the missing indexes DMV's for filtered indexes