Querying Microsoft SQL Server 2012/2014 — Question 59

You administer a Microsoft SQL Server 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?

Answer options

• 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

Correct answer: B

Explanation

The correct answer is B because the REORGANIZE command allows for online defragmentation of the index, which means it can be performed without taking the index offline. The other options either require taking the index offline (as with REBUILD) or do not specifically address the fragmentation issue without dropping the existing index.