MS SQL Server Interview Question:

What Is the Impact on Other User Sessions When Creating Indexes?

Submitted by: Administrator

If you are creating a new index on a table with existing data, all existing rows will be indexed as part of the CREATE INDEX statement. If the table is large, the indexing process could take some time. The impact of this indexing process on other user sessions is based whether SQL server is using the Offline mode or Online mode.

Be default, SQL Server performs indexing operations in Offline mode, where table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

SQL Server Enterprise Edition supports indexing operations in Online mode, where other user sessions will not be impacted.

However, SQL Server Express Edition does no support the Online mode. If you try it, you will get an error as shown below:

CREATE INDEX ggl_links_url ON ggl_links_indexed (url)
WITH (ONLINE = ON);
GO
Online index operations can only be performed in Enterprise
edition of SQL Server.

Submitted by: Administrator