When index is created or recreated it usually decreases performance of database. Either SQL takes long time for response or it does not response at all as transactions are blocked. When new table or database goes live it is not possible to find out exactly how many indexes are needed. After running queries on near to production data it is possible to find out which index can perform better. It is important in highly sensitive application to have data always available.
SQL Server 2005 and later versions have provided feature called “Online Indexing”. Everytime index is updated it puts lock on table where index operations are happening. Depending on the situation SQL Server puts schema lock or shared lock while index is modified. If feature of “Online Indexing” is used SQL Server will behave normally when Index is modified.
One shortcoming of “Online Indexing” is when this feature is on, it takes little more time to complete index modification due to it lets database its normal operation continue. If there is no need of database or table offline indexing feature should be used as it is faster to complete whole process.
Let us see example of how index is specified with ONLINE option along with it.
CREATE INDEX idx_Table ON MyTable (MyColumn) WITH (ONLINE = ON)
The default value for the ONLINE option is OFF. This feature is not available with datatypes like TEXT, NTEXT, IMAGE or binnary.
Reference : Pinal Dave (https://blog.sqlauthority.com)
