Does creating a nonclustered index on a SQL Server 2005 table prevent selects?

Question

I'd like to create an index on a view I have but I need to make sure the data can still be read while the index is being created. I was reading an article that suggested that when creating a nonclustered index that the data is still readable if you specify the ONLINE=ON option (example below):

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

Am I understanding this correctly? Is there any potential issues I should be aware of before I create indexes on a view that needs to be readable while I create my index?

Answer 1

Online index creation and rebuild are available only on Enterprise Edition. See How Online Index Operations Work and Guidelines for Performing Online Index Operations.

There are some restrictions, most notable ones being:

• clustered index must be created/rebuilt offline if they contain any BLOB fields (image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml).
• intitial clustered index on a view must be created offline.

You must ensure your database have enough space to perform the online index operation, as it requires about 1.5 times the size of the table in addition to the current size. During the online index creation the table exist twice in the database, hence the extra space needed.

Since your case falls in the excluded category (initial clustered index on a view) then you need not worry about online indexes. You must use an offline index operation.

BTW you must also be aware that indexed views are considered by the optimizer only in Enterprise Edition. On lower editions one must specify the NOEXPAND clause in the view to leverage a possible index on the view.

Answer 2

There's more information on the msdn articles about CREATE INDEX and online index operations that has lots of information about it.

There should be no issues with it, if you're only doing SELECTs and UPDATEs. Not so sure about backups, maybe best to try it on a test system and see?