Sql-server – SQL Server not Using Index

I am absolutely stumped as to why my query is not using what I think is a selective index.

My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many Phone numbers. A Claim can have a Status and a Phone Number has a Type.

I have added an index on the Claim for the Status and it includes the ContactID.

create index Status on tClaim(Status) include (Name,ContactID)

I have added an index on the Phones for the ContactID and Type that includes the Number.

create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)

I am trying to write a query that returns all Claims that have a status of 'Won' and the corresponding 'Home Phone' for the Claim's Contact. I have tried it 2 ways. One including the join to Contacts and one without. Neither generate a plan that I expect.

select 
    c.ID,
    c.Name,
    p.Number 
from
     tClaim c 
     left join tContactPhone p on
        c.ContactID=p.ContactID and p.Type='Home'
where
    c.Status = 'Won'

select 
    c.ID,
    c.Name,
    p.Number
from
     tClaim c
     inner join tContact co on  
        co.id=c.ContactID
     left join tContactPhone p on
        co.ID=p.ContactID and p.Type='Home'
where
    c.Status = 'Won'

The plan I am getting back, refuses to use the tContactPhone.ContactID_Type. It suggests indexing by Type, which doesn't make sense, because it seems less selective than the ContactId.

Paste The Plan

Here is the script I used to create a sample data set to test. Please note my actual data set is much larger, named better, and has a lot more fields; but this is distilled down to replicate my situation [AKA I don't even like the naming conventions and data generation, but it gets the job done :)]

/*
        Create Tables and Constraints
*/
CREATE TABLE tContact(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    CONSTRAINT [pkey_tContact] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)
GO

CREATE TABLE tContactPhone(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ContactID] [int] NOT NULL,
    [Type] [nvarchar](25) NOT NULL,
    [Number] [nvarchar](12) NOT NULL,
    CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)
GO

ALTER TABLE tContactPhone  WITH CHECK ADD  CONSTRAINT FK_tContactPhones FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO

ALTER TABLE tContactPhone CHECK CONSTRAINT FK_tContactPhones
GO

CREATE TABLE tClaim(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ContactID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Status] nvarchar(10) not null,
    CONSTRAINT [pkey_tClaim] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)
GO

ALTER TABLE tClaim  WITH CHECK ADD  CONSTRAINT FK_tClaim FOREIGN KEY(ContactID)
REFERENCES tContact ([ID])
GO


/*
        Add Test Data
*/
declare @Count int = 0
declare @ContactID int =0

while(@Count<100000)
begin
    set @Count = @Count+1
    insert into tContact(Name)
    select 'Name' + convert(nvarchar(10),@Count)    

    set @ContactID= SCOPE_IDENTITY()

    insert into tContactPhone(ContactID,Number,Type)
    select @ContactID,@Count+1,'Home'
    union select @ContactID,@Count+1,'Cell'

    insert into tClaim(ContactID,Name,Status)
    select @ContactID, convert(nvarchar(10),@ContactID)+'_ClaimName',case @Count % 25 when 0 then 'Won' else 'Closed' end
end

/*
        Add Indexes for Queries
*/
create index Status on tClaim(Status) include (Name,ContactID)
create index ContactID_Type on tContactPhone(ContactID,Type) include (Number)