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 NOT NULL,
CONSTRAINT [pkey_tContact] PRIMARY KEY CLUSTERED
(
[ID] ASC

You can get almost as good a plan with fewer indexes if you cluster tContactPhone by (ContactID,ID) instead of having a clustered index on ID and a seperate non-clustered index on ContactID. eg

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

This is generally a better-performing pattern for "child tables" as the clustered index also supports the foreign key.

StackExchange Database Administrators Q#292020, answer score: 2