Search
SQL Server Expert Blogs
Maintain High Availability of Large Tables during Index Creation
Creating non-clustered indexes on very large tables will make the table unavailable to updates as SQL Server puts a Shared(S) lock on the table. You can do Selects with Shared locks, but for high availability this won't do. If you need to create a clustered index, it gets worse...SQL Server puts Schema Modifications (SCH-M) lock on the table which prevents all access to the table. Hopefully you never have to create a clustered index on a production table, but if you do SQL Server 2005 Enterprise has a solution with online index creation. The syntax just adds with (ONLINE=ON) to the end of the create statement.
The SQL engine will still put Schema locks on but only at the start and end of the creation.
