Connect Digest : 2011-05-16
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.
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.
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:
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!
#329805 : Enhancements to the missing indexes DMV's for filtered indexes