Administering Microsoft SQL Server 2012/2014 Databases — Question 30

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?

Answer options

• 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

Correct answer: A

Explanation

The correct answer is A because the REORGANIZE operation allows for online defragmentation, enabling user queries to access the index concurrently. Option B and C involve rebuilding the index, which would lock it and prevent access during the operation. Option D creates a new index but does not address the existing fragmentation issue appropriately.