Home » Microsoft » 70-762 » Which Transact-SQL statement should you run?
You run the following Transact-SQL statement:
There are multiple unique OrderID values. Most of the UnitPrice values for the same OrderID are different.
You need to create a single index seek query that does not use the following operators:
• Nested loop
• Sort
• Key lookup
Which Transact-SQL statement should you run?
A. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Description, Quantity)
B. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Quantity)
C. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice, Quantity)
D. CREATE INDEX IX_OrderLines_1 ON OrderLines (UnitPrice, OrderID) INCLUDE (Description, Quantity)
Correct Answer: A
Explanation/Reference:
Explanation:
An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.
Note: All data types except text, ntext, and image can be used as nonkey columns.
Incorrect Answers:
C: Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns.
D: The most unique column should be the first in the index.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017
Can’t answer this without knowing which query we are tuning.