Greetings,
I am learning about database index right now, thru reading "Stairway to SQL Server Indexes: Step 9..." http://qa.sqlservercentral.com/articles/Stairway+Series/72441/. There is some thing I don't understand. It said with query below:
SELECT C.LastName, C.FirstName, C.MiddleName, C.Title, H.SalesOrderID, H.OrderDate
FROM Person.Contact C
JOIN Sales.SalesOrderHeader H ON H.ContactID = C.ContactID
WHERE Suffix is null
ORDER BY Title
the query plan would be as figured as number 5. And I already test it in SSMS.
I thought the query plan should be still like figure 4 (not changing). This is because index IX_ContactID which added later does not give more advantage (lack field SalesOrderID to show for final output). How come with new index IX_ContactID and Clustered Index of table Contact, we can produce field SalesOrderID for final result?
Please give me enlightenment here.
Thanks in advance.