Unique filtered index and indexed view are ignored by optimizer

Question

Suppose I have a table schema like this:

Partners table

ID      Name
1       Test

Partners_Codes table

Partner_ID   Code
1            'Test_Code'

I also have unique clustered index on Partner_ID in Partners_Codes:

create unique clustered index IX_Partners_Codes on Partners_Codes (Partner_ID);

Now, when I do query like this:

select
    P.ID, P.Name
from dbo.Partners as P
    left outer join dbo.Partners_Codes as PC on PC.Partner_ID = P.ID;

SQL Server optimizer is smart enough to see the indexes on Partners_Codes and not to query Partners_Codes at all and this good for performance.

Now suppose I have to add an Active column to Partners_Codes, so I could have many codes for each Partner, but only one of this codes could be Active.

Partners_Codes2 table

Partner_ID    Code               Active
1             'Test_Code'             1
1             'Test_Code_old1'        0
1             'Test_Code_old2'        0

There're 2 approaches I've tried - to use filtered index on Active = 1 or to create view and unique index on this view:

create table Partners_Codes2 (Partner_ID int, Code nvarchar(128), Active bit);

create view vw_Partners_Codes2
with schemabinding
as
  select
      Partner_ID, Code
  from dbo.Partners_Codes2
  where Active = 1;

create unique clustered index IX_vw_Partners_Codes2 on vw_Partners_Codes2 (Partner_ID);

create table Partners_Codes3 (Partner_ID int, Code nvarchar(128), Active bit);
create unique clustered index IX_Partners_Codes31 on Partners_Codes3 (Partner_ID, Code);
create unique nonclustered index IX_Partners_Codes32 on Partners_Codes3 (Partner_ID) include(Code, Active) where (Active = 1);

But for both approaches SQL Server optimizer will query Partners_Codes table even if it should know there's only one or zero row in table and I'm not fetching any data from the table.

My actual schema is bit more complicated, and I don't want to split data into several tables. The question is - is it possible to create filtered index or indexed view so optimizer will use it in the case shown?

sql fiddle demo

related links:

• Optimizer Limitations with Filtered Indexes
• Filtered index condition is ignored by optimizer

Answer 1

I've just read this article to the end and I've found that I've missed that last string:

The NOEXPAND hints are needed even in Enterprise Edition to ensure the uniqueness guarantee provided by the view indexes is used by the optimizer.

I think this one should be written with bold 50 font at the beginning of the article.

So I've just changed my query like this:

select
    P.ID
from dbo.Partners as P
    left outer join dbo.vw_Partners_Codes2 as PC with (noexpand) on PC.Partner_ID = P.ID;

and it works fine!!!

sql fiddle demo

Some more links:

• How filtered indexes could be a much more powerful feature

• Optimizer Limitations with Filtered Indexes