Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables! - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables!

Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database.

Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation, in this regard review the blogs by SQLQuery Processing team about offline & online index operation. Still you cannot perform ONLINE index operations that are four kind of indexes with the exceptions such as:

  • Disabled indexes
  • XML indexes
  • Indexes on local temp tables
  • Partitioned indexes
  • Clustered indexes if the underlying table contains LOB(image/text) data types
  • Nonclustered indexes that are defined with LOB (image/text) data type columns

So as per the subject link say if you need to access the data while creating index on a large table to minimize the down time, here is the trick. By default SQL engine puts up schema modification lock (SCH-M) on that table that prevents all the access (SELECT, UPDATE, DELETE) to the data until it finishes the CREATE INDEX operations. This is bit different to the action when you try to create NON CLUSTERED index which puts up shared lock (S) that will also prevent any data modifications only but still it is available for data readability.

Similarly when using online index operations on tables, SQL Server will still put an SCH-M lock for a clustered index or Shared (S) lock for a non-clustered index on the underlying table, but only for a very short period of time that is nothing but during the start and end phase of the index operation. So when you need to create index on a large table within online operations better to perform the ONLINE operation by using:

ON SQLTips (tip) with (ONLINE=ON)


Bear in mind this is only available in Enterprise Edition of SQL Server and not in other editions, by default you should perform such index operations during less traffic hours on the database for the better access for querying and updating of the underlying table during the index creation process.

(cross relation from my blog, with update)

Published Monday, February 25, 2008 7:53 AM by ssqa.net


No Comments