Exam 70-762 topic 1 question 126 discussion

The question is too wide to answer it. It depends how you query the data from the "new NCI", the list should be ordered in the same way how you require in output to prevent "sort", It should containt all all requested columns for output in "list&included" (except the primary key, because it already included in NCI) -> to prevent lookups. Also if you would like to SEEK in the index the predicate should coresponded with the list of NCI. So the question is missing the "query" statement for answer it.

Answer should be A. Because it follows the correct order for the key columns plus it has both description and qty as included columns. Whereas D has the ordering of the key columns wrong!

The question is not complete. Without seeing the query we cannot say anything. For example, if the query is: SELECT Description, Quantity, UnitPrice FROM OrderLines WHERE OrderId = @OrderId and UnitPrice > 5, then we should chose A. Howevere, if the query is SELECT Quantity, UnitPrice FROM OrderLines WHERE OrderId = @OrderId and UnitPrice > 5, then we should chose B. If the query is SELECT Quantity, UnitPrice FROM OrderLines WHERE OrderId = @OrderId and UnitPrice > 5 AND Quantity > 10, then we should chose C. D is wrong in any case.

I am between A and D and would go for A, because I think there are nested loops from D. Do you agree?

A and B are equally valid without knowing if we require Description C - the Key size is getting unnecessarily large. D - While UnitPrice is unique (within the Order), when we have 1000 Orders, we are not going to have 1000 unique UnitPrice values. And I don't imagine cases where we would want to query or join on the UnitPrice (return only), rather we would to query on OrderId. The only reason to include UnitPrice in the key is if you wanted to return values in UnitPrice order and you have the requirement to not use the SORT operator. Real World Solution - CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID) INCLUDE (UnitPrice, Description, Quantity) Take the hit of auto-added "uniqueifier", it's 4 bytes, compared to 9 bytes require by UnitPrice. Did you notice that none of the suggested solutions were CREATE UNIQUE INDEX?

Since it says "Most of the UnitPrice values for the same OrderID are different", one has to assume that some values for OrderID does have duplicate UnitPrice values, no? In that case, by the process of elimination, only C seems to work out. Or is there a mistake somewhere?

DROP INDEX PreferredName ON Application.People; GO CREATE NONCLUSTERED INDEX PreferredName_Include_FullName ON Application.People ( PreferredName ) INCLUDE (FullName) ON USERDATA;

"All data types except text, ntext, and image can be used as nonkey columns." Therefore, it cannot be A or D.

There are more UnitPrice unique values than unique OrderId. D is correct