You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You
discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to
reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you
use?
A.
CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP
EXISTING
B.
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C.
ALTER INDEX ALL ON OrderDetail REBUILD
D.
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Explanation:
http://msdn.microsoft.com/en-us/library/ms188388.aspx
b
B.
Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online. To rebuild online a index use the “with (ONLINE = ON)” option.
I think you can only REBUILD ONLINE in the enterprise edition, although the edition is not specified in the question. But you are correct that you would have to specify ONLINE within the syntax.
REORGANIZE a rowstore index
For rowstore indexes, REORGANIZE specifies to reorganize the index leaf level. The REORGANIZE operation is:
Always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
Not allowed for a disabled index
Not allowed when ALLOW_PAGE_LOCKS is set to OFF
Not rolled back when it is performed within a transaction and the transaction is rolled back.
b
B