You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction.

You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process.

Which Transact-SQL batch should you use?

A. ALTER INDEX IDX_AccountTransaction_AccountCode ON

AccountTransaction.AccountCode REORGANIZE

B. ALTER INDEX ALL ON AccountTransaction REBUILD

C. ALTER INDEX IDX_AccountTransaction_AccountCode ON

AccountTransaction.AccountCode REBUILD

D. CREATE INDEX IDXAccountTransactionAccountCode ON

AccountTransaction.AccountCode WITH DROP EXISTING

Explanation:

Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it-s able to just stop where it is (it doesn’t have a giant operation to rollback).

References: https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/