Your Prod database resides on an instance of SQL Server 2008. The Prod database contains a SalesHeader table that is used by one of your applications. The SalesHeader table is defined as follows:
Currently, the SalesHeader table has a clustered index on the SalesOrderID column, and no other indexes. You want to create indexes to provide best performance to your application.
Your application allows users to perform the following actions:
• Searching for an order with a specified order number
• Searching for orders within a specified order number range
• Searching for orders that belong to specific customers
• Searching for orders with a specified customer account number
The application will use the SalesOrderID and CustomerID columns in joins to retrieve data from other tables. Which index or indexes should you create?
A. a full-table nonclustered index on the CustomerID column and a filtered index on the AccountNumber column
B. a clustered index on the CustomerID column
C. a nonclustered index on the CustomerID column and a nonclustered index on the AccountNumber column
D. a composite non-clustered index on the CustomerID and AccountNumber columns
Correct Answer: C
Explanation/Reference:
In this scenario, the application needs to search based on the order number and a range of ordered numbers. Searches on the order number would already be optimized because a clustered index already exists on the SalesOrderID column. To support searches by customer, you should create a nonclustered index on the CustomerID column, and to support searches by account number, you should create a nonclustered index on the AccountNumber column.
You cannot create a clustered index on the CustomerID column because the table already contains a clustered index on the SalesOrderID column. A table can only have one clustered index, because the index represents the physical order of the rows in the table.
You should not create a composite non-clustered index on the CustomerID and AccountNumber columns. A composite index is helpful when searching for both columns, but in this scenario, the application does not allow searching by both customer ID and account number.
You should not create a full-table nonclustered index on the CustomerID column and a filtered index on the AccountNumber column. Although a nonclustered index on the CustomerID column would improve searches for specific customers, there is no need to create a filtered index on the AccountNumber column. A filtered index is a nonclustered index that is defined including a WHERE clause to optimize the index for queries that access specific subsets of the data.
