Provisioning SQL Databases — Question 1

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?

Answer options

• A. ALTER INDEX IX_OrderDateON CustomerOrder DISABLE
• B. ALTER INDEX IX_OrderDateON CustomerOrder ENABLE
• C. ALTER INDEX IX_OrderDateON CustomerOrder REORGANIZE
• D. ALTER INDEX IX OrderDateON CustomerOrder REBUILD

Correct answer: C

Explanation

The correct answer is C, as the REORGANIZE command is designed to defragment an index while keeping it online, which is essential in this scenario. Options A and B do not address fragmentation, and option D would take the index offline during the rebuild process, which does not meet the requirement.