While a secondary index is being created or dropped, the table is locked in shared mode. That is, any writes to the table are blocked, but the data in the table may be read. When you alter the clustered index of a table, however, 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 command must always wait for currently executing transactions that are accessing the table to commit or rollback before it can proceed. In addition, ALTER TABLE commands 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 command that creates a secondary index begins executing, queries may access the table for read access, but may not update the table. If an ALTER TABLE command is changing the clustered index, all queries must wait until the operation completes.

A newly-created secondary index contains only data that is current in the table as of the time the CREATE INDEX or ALTER TABLE command begins to execute. Specifically, a newly-created index contains only the versions of data as of the most-recently committed transactions prior to the creation of the index. The index thus does not contain any rows that were deleted (and therefore marked for deletion) by transactions that completed before the CREATE INDEX or ALTER TABLE began. Similarly, the index contains only current versions of every row, and none of the old versions of rows that were updated by transactions that ran before the index was created.

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.