You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
You need to optimize the CK_Sales database to support a redesigned stored procedure. You need add a key column to an existing clustered index on the OrderDetails table but you must ensure that the table remains available to users while the index is built.
What should you do?
A.
Run the CREATE INDEX statement with the DROP EXISTING and ONLINE options set to ON.
B.
Disable the index. Then run the CREATE INDEX statement with the DROP EXISTING and ONLINE options set to ON
C.
Run the ALTER INDEX statement with the REORGANIZE keyword and the ONLINE option set to ON.
D.
Run the ALTER INDEX statement with the REBUILD keyword and the ONLINE option set to ON.
Explanation:
You must drop the clustered index and recreate it if you want to add a column to an existing clustered index. You can drop and recreate an existing index using the CREATE INDEX statement and setting the DROP EXISTING option to ON. To ensure that users can access the table while the index is being built, you must also set the ONLINE option to ON.
Incorrect Answers:
B: A clustered index cannot be rebuilt online if the index is disabled. C, D: You cannot use the ALTER INDEX statement to add a column to a clustered index. You must drop the clustered index and recreate it if you want to add a column to an existing clustered index.