You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non-business hours

HOTSPOT

You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non-business hours.

You run the following Transact-SQL statement:

You analyze the execution plan for the statement. (Click the Exhibit button). Execution Plan

You need to add an index that optimizes performance.

View Answer

Answer:

Explanation:

Box 1: Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient.

The Database Engine can move equally efficiently in either direction. An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

Box 2: You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. The Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

References: https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx