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:
http://msdn.microsoft.com/en-us/library/ms188388.aspx
B
Answer is B, rebuild will drop and recreate the existing index therefore locking and taking the table “offline”.
B
B. But actually the syntax is wrong, it should be:
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail REORGANIZE
if OrderDatails is in the default schema, or if the schema of the object is Sales:
ALTER INDEX NCI_OrderDetail_CustomerID ON Sales.OrderDetail REORGANIZE
The column name should not be included.