Administering a SQL Database Infrastructure — Question 123

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

Correct answer: A

Explanation

The correct answer is A because the REORGANIZE operation allows for online defragmentation, enabling user queries to utilize the index during the process. Options B and C involve rebuilding the index, which typically requires exclusive access and would block user queries. Option D is incorrect as it attempts to create a new index rather than defragmenting the existing one.