These are an interesting feature. There are many uses for these in larger systems and on larger tables. Normally an index is built over an entire table. With a filtered index, one or more predicates is specified when the index is created and the index only contains data that satisfies the predicates. The columns involved in the predicate do not have to be part of the index key, or the index include columns.<\/p>\n A use I can see for this is a history table, where many months of data is stored within the table, but only the current month’s data is extensively queried. Previously any index had to be created over the entire table, potentially including lots of rows that would never be queried. Now, the indexes can be created only on the activly queried portion of the table, reducing the size of the index tree and the space taken on disk<\/p>\n I need to do some investigation on how well the optimiser can match queries to filtered indexes. Expect more written on that point in the future.<\/p>\n Execution plan<\/strong><\/p>\n There have been some interesting and useful changes in how the execution plan is displayed in management studio.<\/p>\n I say displayed, because the structure of the XML plan has not changed from SQL 2005. The XML Schema definition is the same and SQL 2005’s management studio can read and display an execution plan. This is good in one sence, in that xpath queries that were developed for the 2005 showplan format will still work in SQL 2008. On the other hand, it means that the new 2008 features (sparse columns, filtered indexes) won’t be flagged as such in the execution plans.<\/p>\n Missing indexes are now flagged in the graphical execution plan.This may or may not be a good thing. It does increase the visibility of the lack of indexes, however the missing index report within an execution plan is just the optimiser’s quick guess as to what index would have made the query more efficient. There may well be a very similar index already existing. My concern is that this feature will result in lots of useless or near useless indexes created. I suppose it can’t be worse than no indexes though.<\/p>\n The execution plan now shows the number of executions that occurred for a particular operator, as well as the number that was estimated. This has been missing since Query Analyser and I am very glad to see it coming back. Knowing whether the sort executed once or a thousand times does really help in identifying the cause of slowness.<\/p>\n![\"\"](\"https:\/\/www.sqlinthewild.co.za\/images\/FilteredIndex.PNG\")
<\/a><\/p>\n