Drag and Drop Question You have a database named DB1. DB1 contains a table named Sales.SalesPerson that has an index named AK_SalesPerson_rowguid. Queries that do not use the index take…

Question

Exhibit

Answer Area

Explanation

Explanation

The correct statement being constructed is:

ALTER INDEX AK_SalesPerson_rowguid ON Sales.SalesPerson REBUILD WITH (ONLINE = ON)

Why This Arrangement?

The key constraint in the question is: the index must remain usable during defragmentation. That rules out a simple REBUILD and requires the ONLINE = ON option.

Item-by-Item Breakdown

1. REBUILD

ALTER INDEX supports two defragmentation operations:

• REORGANIZE - lightweight, always online, but only effective for mild-to-moderate fragmentation
• REBUILD - drops and recreates the index; more thorough, required for severe fragmentation

Since the question states severe fragmentation, REBUILD is the correct choice. REORGANIZE would be insufficient.

2. ONLINE

This is the option name inside the WITH (...) clause. The full syntax is:

REBUILD WITH (ONLINE = ON)

ONLINE tells SQL Server to allow concurrent user access to the table and index during the rebuild. Without it, an offline rebuild takes a schema modification lock, blocking all queries.

3. ON

This is the value assigned to the ONLINE option. Setting it to ON is what actually enables online operation. Setting it to OFF (the default) would cause the rebuild to block queries - violating the requirement.

Common Mistakes

Key Takeaway

The question tests two distinct SQL Server concepts together:

1. Severity of fragmentation -> determines REBUILD over REORGANIZE
2. Availability during maintenance -> requires WITH (ONLINE = ON)

No community discussion yet for this question.