I have a table that has following structure, as you can see, there are one clustered index and two non-clustered indexes, one non-clustered index is IX_ParentId_Include, and another one is FLIX_ParentId_Include

they are same apart from the second index is filtered.

CREATE TABLE [dbo].[PhotoRepo] (
    [PhotoRepoId]              INT            IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
    [TypeId]                   TINYINT        NULL,
    [ParentId]                 INT            NULL,
    [RemoteLocation]           VARCHAR (4000) NOT NULL,
    [UploadedAt]               DATETIME       NOT NULL,
    [UploadedBy]               VARCHAR (255)  NULL,
    [FileSize]                 INT            NOT NULL,
    [DefaultChild]             BIT            NOT NULL,
    [RemoteLocationUploadedAt] DATETIME       NULL,

    ON [dbo].[PhotoRepo]([PhotoRepoId] ASC);


    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId]);

    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId])
    WHERE TypeId <> 7;


Now when I run this query with execution plan enabled, the query optimizer doesn't use filter index, which I expect it should be more efficient one, it uses IX_ParentId_Include. It uses filter index only when i either force it to, or delete IX_ParentId_Include

Anyone knows why?

FROM   PhotoRepo
    typeid <> 7 and
    parentid = 123

PS: there is 30% rows that have typeid = 7


1 Answer 1


Why do you think it should use the filtered index? You've used SELECT * in your query and your predicate involves more than just the filter condition. SQL Server is going to have to look up those columns in another index that covers it anyway (your SELECT * plan should include a key lookup to retrieve those other columns).

SQL Server will decide based on stats/cardinality (e.g. how many rows currently match the filter, how many rows total) and decide at that point which index it should use. In this case, it is probably a coin flip, because it can seek to the 19 rows that match the ParentId predicate, and not cost any more in I/O to do so. It might choose the filtered index if it represented a much smaller portion of the table than 30%. You should see what it does, though, if you add OPTION (RECOMPILE) to the query. Depending on your parameterization settings, it may be using a plan that was cached with a different parameter value (e.g. one that doesn't match the index filter).

In this case, I believe you are seeing this behavior because, well, RTM. Please install Service Pack 3 and consider Cumulative Update 16. I browsed quickly and didn't see anything relating to filtered indexes, but this could be a by-product of some other optimizer wonkiness that has been fixed in the meantime. In any case, I can't really think of any good reason why you should continue running RTM at this time.

Could also be related to these are other "bugs known gaps in functionality":

I've also blogged about a whole slew of limitations with filtered indexes, and pointed to a ton of related Connect items. Most of these remain unfixed and, for the most part, filtered indexes have received absolutely no attention since they were first introduced. If you're going to use these objects, and expect them to be used to speed up your queries, you'd be wise to familiarize yourself with all of these limitations, and consider using an index hint to force them to be used in some scenarios. Caveats with using an index hint, of course:

  1. It may not work better (you don't always know better than the optimizer); it may work better for some queries but not all. Usually SQL Server will use the filtered index if (a) it can, and (b) it deems it to be the most efficient access method.
  2. Queries will fail if the index is dropped, or re-created with a new name.
  • Okay I added OPTION (RECOMPILE), and I also changed select all to select a single column 'ParentId', execution plan still shows IX_ParentId_Include is in use
    – Ming
    Commented Apr 8, 2014 at 14:45
  • @Ming did you try NOT USING SELECT *? Commented Apr 8, 2014 at 14:48
  • Yes, I changed select * to select parentid, same results (with recompile option)
    – Ming
    Commented Apr 8, 2014 at 14:50
  • 1
    @Ming How many rows does the table have and how many rows do have parentid = 123? If the numbers are small, I guess the difference in efficiency of the two indexes is negligible. Commented Apr 8, 2014 at 14:54
  • There are 19 of 509720 rows that has parentid = 123 and typeid <> 7. Just noticed if I run select parentid from photorepo where typeid <> 7, it does a index scan on filtered index.
    – Ming
    Commented Apr 8, 2014 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.