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).
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/