You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named ABCDB.
The ABCDB database is used by an in-house application named ABCApp3 that queries a readonly table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly.
You suspect query the application uses is causing the problem. You analyze the query and
discover that column referenced in the WHERE clause is not part of the clustered index. You also
notice that the query returns five columns, as well as a COUNT (*) clause grouped on the five
columns.
How would you improve the efficiency of this query?
A.
You should replace the query with recursive stored procedure.
B.
You should replace the COUNT (*) clause with a persisted computed column.
C.
You should create nonclustered indexes on all columns used in the query.
D.
You should create a filtered index on the column used in the WHERE clause.
E.
You should add an INCLUDE clause to the clustered index.
F.
You should create a columnstore index on all columns used in the query.
G.
You should create a unique clustered index on the column used in the WHERE clause.
Explanation: