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?
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.