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.

SELECT a.RecordID SubmissionID,
  a.[DateSubmitted],
  a.[FinalAction],
  a.[FinalActionReason],
  a.[FinalActionDate],
  b.GradeSubmissionListID,
  b.[Action],
  b.ActionReason,
  b.ActionDate
FROM [dbo].[GradeSubmissionList] a
  LEFT JOIN dbo.GradeSubmissionApproveRejectLog b
  ON a.RecordID = b.GradeSubmissionListID
WHERE a.EdpCode = '1314ACT1258'
ORDER BY a.DateSubmitted

This produces the following execution plan,

enter image description here

I want to get rid all Clustered Index Scan. When I hover the first clustered index scan, it generated this image,

enter image description here

It looks like it's using the Primary Key index,

ALTER TABLE [dbo].[GradeSubmissionList] 
ADD CONSTRAINT [GradeSubmissionList_pk_RecordID] PRIMARY KEY CLUSTERED 
([RecordID] ASC) ON [PRIMARY]

So I created one based on this article: Why use the INCLUDE clause when creating an index?

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(RecordID, FinalApprovedRejectedBy, EdpCode , DateSubmitted)
INCLUDE ( FinalAction, FinalActionDate, FinalActionReason );

I am expecting that the sql statement will now be converted into Index Seek, but shows out it's still scanning the entire row.

enter image description here enter image description here

What are the thing the I need to do in order to make this work? If there are things that you want to know about my situation please do ask.

share|improve this question
1  
You need to have EmpCode as you first field in your Index. This will (if the fields is mostly unique) force an Index Seek. –  PollusB Oct 18 '13 at 2:27
    
@PollusB EmpCode is not unique. I'll try to interchange it and let you know. –  Rossana Oct 18 '13 at 2:29
    
You will find my answer below. –  PollusB Oct 18 '13 at 2:46
add comment

1 Answer

up vote 2 down vote accepted

Try to create this index:

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(EdpCode, DateSubmitted)
INCLUDE (FinalApprovedRejectedBy, FinalAction, FinalActionDate, FinalActionReason );

Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article SQL Server Index Design Guide

share|improve this answer
2  
Answering "why?", will make this a more informative answer... –  Shawn Melton Oct 18 '13 at 2:24
    
You got it. Having EmpCode as first then DateSubmitted, should do the trick. If the index selectivity is good, this index will be seeked, else it will still be scanned but the cost might be lower. Check your plans and compare. –  PollusB Oct 18 '13 at 2:28
    
Providing a link to a guide is not really answering the "why" in the OP's situation...just my opinion. –  Shawn Melton Oct 18 '13 at 2:32
    
why is RecordID not included on the keylist? it is used in the join right? –  Rossana Oct 18 '13 at 2:34
    
@ShawnMelton I tried to explain "why" as i could. And actually it's very complex question which worth writing an entire article. Unfortunately, I'm not very good at it. If you want you can edit my answer or give yours. Thanks for critique ))) –  Igor Oct 18 '13 at 2:44
show 7 more comments

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.