You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com makes use of a database named CK_DATA. CK_DATA is located on a server named Certkiller -DB01.
You have been informed by the CIO that a table scan on the Products table in CK_DATA causes a slow query. This is a large table that is used regularly. You investigate and discover that the query contains the following statement:
SELECT PrName, PrDesc FROM Products WHERE PrID = <value> However, the Products table should at all times remain available to users.
What should you do?
A.
You should execute the following statement:
USE CK_DATA
GO
CREATE INDEX index1 ON Products(PrID)
INCLUDE(PrName, PrDesc)
WITH (ONLINE = ON)
GO
B.
You should ensure that all statistics are updated on the Products table in CK_DATA.
C.
The CREATE STATISTICS statement in CK_DATA must be used to create missing statistics on the PrID column of the Products table.
D.
You should execute the following statement:
USE CK_DATA
GO
CREATE INDEX index1 ON Products(PrID, PrDesc, PrName)
E.
You should set the priority boost server option to 1.
Explanation:
You want to create an index on the columns 1 to 3 in order to have quicker response when querying data in columns 1 and 2 based on a value in column 3.
Syntax
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index ON object (column [ASC | DESC] [,…n ] )
[INCLUDE (column [ ,…n] ) ]
[WITH (option [ ,…n] ) ]
[ON { partition_scheme ( column ) | filegroup | default } ]
[;]