While a secondary index is being created or dropped, the table is locked in shared mode. Any writes to the table are blocked, but the data in the table can be read. When you alter the clustered index of a table, the table is locked in exclusive mode, because the data must be copied. Thus, during the creation of a new clustered index, all operations on the table are blocked.
Before it can start executing, a CREATE INDEX or
ALTER TABLE statement must always
wait for currently executing transactions that are accessing the
table to commit or rollback before it can proceed. In addition,
ALTER TABLE statements that create
a new clustered index must wait for all SELECT
statements that access the table to complete (or their containing
transactions to commit). Even though the original index exists
throughout the creation of the new clustered index, no
transactions whose execution spans the creation of the index can
be accessing the table, because the original table must be dropped
when clustered index is restructured.
Once a CREATE INDEX or ALTER
TABLE statement that creates a secondary index begins
executing, queries can access the table for read access, but
cannot update the table. If an ALTER
TABLE statement is changing the clustered index, all
queries must wait until the operation completes.
A newly-created secondary index contains only the committed data
in the table at the time the CREATE INDEX or
ALTER TABLE statement begins to
execute. It does not contain any uncommitted values, old versions
of values, or values marked for deletion but not yet removed from
the old index.
Because a newly-created index contains only information about data current at the time the index was created, queries that need to see data that was deleted or changed before the index was created cannot use the index. The only queries that could be affected by this limitation are those executing in transactions that began before the creation of the index was begun. For such queries, unpredictable results could occur. Newer queries can use the index.