Administering a SQL Database Infrastructure — Question 132

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

Correct answer: B

Explanation

The correct answer is B, as using the REORGANIZE option allows you to defragment the index without taking it offline, thus maintaining availability. Option A is incorrect because it attempts to drop the existing index, which would take it offline. Option C rebuilds all indexes and may take them offline, and option D is incomplete and cannot be executed.