[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Create clustered index on read committed snapshot db

Posted on 2011-05-03
Medium Priority
Last Modified: 2012-05-11
If anyone had experience with read committed snapshot enabled, can I have some advised what would be the status of insert / select statement during the index creation ?
one of the table has no clustered index and we plan to create it during peak hours.
Question by:motioneye
LVL 15

Accepted Solution

Aaron Shilo earned 1000 total points
ID: 35513217
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.

LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 35571943
>> one of the table has no clustered index and we plan to create it during peak hours.

I have used Read Committed Snapshot Isolation set in my environment.
Kindly let us know the mount of data currently available in your table: If it is less, then it won't take much time or impact. And Offline/ Online concepts mentioned above are applicable.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question