You are a database administrator for a toy manufacturing company named NetFx. The company stores all its product-related data in a database named Netfx_data that resides on a SQL Server 2008 server named Server1. A table named Invoice_details exists in the database and contains the details of all the new invoices generated by the company in the last month. The table was created by using the following CREATE TABLE statement:
CREATE TABLE Invoice_details (
Invoice_id int NOT NULL,
Customer_id int NOT NULL,
Invoice_date datetime DEFAULT GETDATE () NULL,
Amount_total int NULL,
Serial_num int IDENTITY (1,1) NOT NULL)
A clustered index exists on the Serial_num column, and a composite nonclustered index exists on the Invoice_id and the Customer_id columns.
An application will be used to execute queries to retrieve all the invoices for a particular customer. The following query is used by the application to retrieve data:
SELECT Customer_id, Invoice_id, Invoice_date
FROM Invoice_details
WHERE Customer_id = 1234
ORDER BY Invoice_date DESC;
You must ensure optimal performance of the query.
Which action should you perform to achieve this?
A.
Create a clustered index on the Customer_id column.
B.
Create a nonclustered index on the Customer_id column.
C.
Create a nonclustered index on the Invoice_date column.
D.
Alter the nonclustered index on the table to include the Invoice_date column.
E.
Alter the nonclustered index on the table to remove the Customer_id column.
Explanation:
You should alter the nonclustered index on the table to include the Invoice_date column. Including the Invoice_date column in the nonclustered index on the table will create a covering index for all three columns in the SELECT list of the query. A covering index on all the columns present in the SELECT list increases the performance of the query because all the data required for the query can be retrieved from the index. Only the index pages containing the index must be scanned to return the required data. This improves performance of the query. You should not create a clustered index on the Customer_id column because a clustered index exists in the table. A table can contain only one clustered index. You should not create a nonclustered index on the Customer_id column. Creating a nonclustered index on the Customer_id column on the table will not improve the performance in this scenario because the SQL Server engine would scan both the nonclustered indexes in the table to retrieve the rows required by the query. A composite nonclustered index including all three columns in the SELECT list should be used in this scenario. You should not create a nonclustered index on the Invoice_date column. Creating a nonclustered index on the Invoice_date column will not improve the performance in this scenario because the SQL Server engine will have to scan both nonclustered indexes in the table to retrieve the rows required by the query. A composite nonclustered index including all three columns in the SELECT list should be used in this scenario. You should not alter the nonclustered index on the table to remove the Customer_id column. Removing the Customer_id column from the nonclustered index will not improve the performance in this scenario. The nonclustered index should instead be altered to include the Invoice_date column. This will create an index that contains all the data required by the query in this scenario.Objective: Performing Data Management Tasks Sub-Objective: Maintain indexes.
References: TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Development > Designing and Implementing Structured Storage (Database Engine) > Indexes > Implementing Indexes > Creating Indexes (Database Engine) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Development > Designing and Implementing Structured Storage (Database Engine) > Indexes > Implementing Indexes > Creating Indexes (Database Engine) > Creating Indexes with Included Columns