You are a database administrator for AIOTestKing.com The company uses a SQL Server 2005 database that contains a table named Production.Product. There are more than 40 columns in the table. The most common queries are shown in the following Transact-SQL statements.
–QUERY 1 SELECT [Name], [ProductLine], [ListPrice], [Class], [Style] FROM [Production].[Product] WHERE [ProductLine] = ‘T’
–QUERY 2 SELECT * FROM [Production].[Product] ORDER BY [Name]
You need to choose the best indexes to support the most common queries to the Production.Product table while minimizing the space required for the indexes. Which two indexes should you use? (Each correct answer presents part of the solution. Choose two.)
A.
Create a clustered index on the Name column.
B.
Create a nonclustered index on the ProductLine column and include the ListPrice, Class and Style columns.
C.
Create a nonclustered index on the Name column.
D.
Create a clustered index on the ProductLine, ListPrice, Class and Style columns.
E.
Create a nonclustered index on the ProductLine, ListPrice columns.
Explanation:
The name is used frequently in a way that would benefit from a clustered index. (ORDER BY [Name]). Next you se in the QUERY 1 that ListPrice, Class, and Style is also used frequently but without an ORDER BY clause attached to them. These would benefit from a nonclustered index on the ProductLine column including the other columns.