In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, SemesterID, ShiftID, OtherSemester).

First Query enter image description here

Second Query enter image description here

Why the second plan uses look up while both OtherSemester and ShiftID are included in non-clustered index? Why the second query does not have the plan like the first query?

  • There are several Non-Clustered Index created by the database engine, including _dta_index_Enrollment_6_1515204498__K3_K4_K1_K2_K16 which does not include ShiftID, But I created another Non-Clustered Index by the name of OtherTablesIDs which includes ShiftID. – Asrar Ahmad Ehsan Oct 14 '18 at 12:38
  • I deleted all non clustered indexes created by the DB Engine and create one non clustered index which includes the ShiftID and OtherSemesterID columns. I run the second query again and it worked used the same execution plan as first query. – Asrar Ahmad Ehsan Oct 16 '18 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.