You administer a Microsoft SQL Server 2012 database that contains a table named
OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is
fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch
should you use?
A.
CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP
EXISTING
B.
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C.
ALTER INDEX ALL ON OrderDetail REBUILD
D.
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms188388.aspx
Ans:
B.
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
0
0
REBUILD has greater system I/O impact and cost… it also places a lock on the table during index rebuild.
REORGANIZE has very minimal system I/O impact, and does not constraint user access during the process.
0
0
from the exam point of view correct is to REORGANIZE to meet the requirement “without taking the index offline”.
In real world index REBUILD can be executed without taking it offline. The SQL version support this solution is Enterprise, Developer, Evaluation.
0
0