Query uses Primary Key Index instead of Nonclustered index defined

I have this simple query and I am confused why the index is not working.

SELECT lop.RecordID ProspectusID,
    c.c_code CourseCode,
    c.c_desc CourseDescription,
    COALESCE(c.c_major, '') MajorName,
    lop.EffectiveYear EffectiveSchoolYear,
    COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount
FROM dbo.ListOfProspectus AS lop
    INNER JOIN dbo.courselist AS c ON lop.CourseCode = c.c_code
    LEFT JOIN dbo.ProspectusContent AS pc ON lop.RecordID = pc.ProspectusID -- <== problem
WHERE lop.CourseCode IN ('BSIT')
GROUP BY lop.RecordID, c.c_code, c.c_desc, c.c_major, lop.EffectiveYear
ORDER BY CourseCode, EffectiveSchoolYear

which generates the following plan,

I have an index defined on dbo.ProspectusContent.ProspectusID.

CREATE NONCLUSTERED INDEX [idx_ProspectusContent_prospectusid] 
ON [dbo].[ProspectusContent]
(
    [ProspectusID] ASC
)

But why is it still using the primary key index?

ALTER TABLE [dbo].[ProspectusContent] 
ADD CONSTRAINT [ProspectusContent_pk_RecordID] PRIMARY KEY CLUSTERED 
(
    [RecordID] ASC
)

If I remove the following computed column: COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount in the SELECT clause, the server generates this plan:

UPDATE

However, when I try to execute a query on table dbo.ProspectusContent alone, it uses the nonclustered index,

SELECT ProspectusID
FROM dbo.ProspectusContent AS pc 
WHERE pc.ProspectusID = 2

By the way, idx_ProspectusContent_prospectusid is the same with idx_dmcasarms_ProspectusContent_prospectusid, just convention name policy.

If there's anything missing detail in my question, please do me so I can update it. Thank you.

The table only has 2 rows. The cost difference between a table scan and an NC seek is negligible. SQL Server is pretty good about finding the cheapest way to getting things done. I'd expect that when the table gets bigger, the NC index will start getting used.

To compare performance, you can force the index to be used with an index hint:

SELECT lop.RecordID ProspectusID,
    c.c_code CourseCode,
    c.c_desc CourseDescription,
    COALESCE(c.c_major, '') MajorName,
    lop.EffectiveYear EffectiveSchoolYear,
    COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount
FROM dbo.ListOfProspectus AS lop
    INNER JOIN dbo.courselist AS c ON lop.CourseCode = c.c_code
    LEFT JOIN dbo.ProspectusContent AS pc WITH (INDEX(idx_ProspectusContent_prospectusid)) ON lop.RecordID = pc.ProspectusID
WHERE lop.CourseCode IN ('BSIT')
GROUP BY lop.RecordID, c.c_code, c.c_desc, c.c_major, lop.EffectiveYear
ORDER BY CourseCode, EffectiveSchoolYear

In production, I would use the hint as a last resort though. Given proper indexes and statistics, the query optimizer can typically build the cheapest plan on its' own.

Read more about hints: http://technet.microsoft.com/en-us/library/ms181714.aspx

StackExchange Database Administrators Q#52711, answer score: 3