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:

.... CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS
ON SQLTips (tip) with (ONLINE=ON)

Go;

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.