Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

enter image description here enter image description here

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:

enter image description here

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

enter image description here enter image description here

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
    
Thank you! I thought the number of records won't matter since I tested a query on the table only and it performs Index Seek. But when I generated test data on it, it performs well. Sorry for ignorance. –  Rossana Nov 3 '13 at 7:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.