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.
Best Answer
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:
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