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, CONSTRAINT [PK_FileList] PRIMARY KEY NONCLUSTERED ([PhotoRepoId] ASC) ); GO CREATE CLUSTERED INDEX [IX_PhotoRepoId] ON [dbo].[PhotoRepo]([PhotoRepoId] ASC); GO CREATE NONCLUSTERED INDEX [IX_ParentId_Include] ON [dbo].[PhotoRepo]([ParentId] ASC) INCLUDE([RemoteLocation], [DefaultChild], [TypeId]); GO CREATE NONCLUSTERED INDEX [FLIX_ParentId_Include] ON [dbo].[PhotoRepo]([ParentId] ASC) INCLUDE([RemoteLocation], [DefaultChild], [TypeId]) WHERE TypeId <> 7; GO
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?
SELECT * FROM PhotoRepo WHERE typeid <> 7 and parentid = 123
PS: there is 30% rows that have typeid = 7