I'm apologizing for an obscure question. Here is the situation.
I have a large table, lets call it Order, with a PK nOrder. Each order is owned by only one customer, and their connection is stored in a cross-reference table OrderCustomer that has its own PK, and indexes nOrder and nCustomer. Customers' data is stored in Customer table, and so on. Pretty standard schema, right? However, is the cross reference table (OrderCustomer) really needed? Why don't we put nCustomer field directly into Order table and save the trip? And so I did, and now when I need to pull a list of order with all the details, including details on customers, their locations, and what not, I have one inner join less. Compare: 1. Select [list of fields] From Order O Inner Join OrderCustomer OCU ON O.nOrder = OCU.nOrder Inner Join Customer C ON C.nCustomer = OCU.nCustomer. 2. Select [list of fields] From Order O Inner Join Customer C ON C.nCustomer = O.nCustomer.
Was there a visible performance improvement #2 over #1? NO.The first query takes 3% of time compare to the second 97%: The long list of fields from Order table was by default included in the nOrder index used in the join, and with now join on nCustomer the plan shows key lookup, instead of clustered index seek.
I know that I need to create a covering index on nCustomer field with all the fields in the output included, but wouldn't this be a duplication of already existing index? Is there a way to still take advantage of the clustered key?