You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a large table that has 20 million rows. The table contains the
following columns: • CustomerNumber • CompanyName • ContactFirstName •
ContactLastName The table currently has single-column nonclustered indexes on the
CustomerNumber, CompanyName, and ContactFirstName columns. An application uses
data from this table. The user interface of the application allows the usage of any one filter
from the following list of filters: • CustomerNumber and CompanyName • CompanyName •
ContactLastName • ContactLastName and ContactFirstName In all cases, the listed order
of the columns is the order in which they will appear in the WHERE clause that is
generated. You need to design an indexing strategy for this table, so that the query
optimizer can quickly perform an index seek when searching through the table data. What
should you recommend?

A.
• Drop all existing indexes. • Create two multicolumn indexes, one on CustomerNumber
and CompanyName and the other on ContactLastName and ContactFirstName.

B.
• Drop all existing indexes. • Create two multicolumn indexes, one on CompanyName
and CustomerNumber and the other on ContactLastName and ContactFirstName.

C.
• Drop the indexes on CustomerNumber and ContactFirstName. • Create two
multicolumn indexes, one on CustomerNumber and CompanyName and the other on
ContactLastName and ContactFirstName.

D.
• Drop all existing indexes. • Create a new index on ContactLastName and
ContactFirstName. • Create a multicolumn index on CustomerNumber and CompanyName.

Explanation: