Rebuild index while insert/update is also available

Some times we face such a situation that we have data in GB of size and we want to rebuild our index which is applied on that table. but in previous editions from MS-Sql server2005 this was not possible to provide insert/update facility while performing rebuild index operation.

but from sql server2005 we have got this very good facility to make insert/update available (working) while rebuild index is under progress.

to do so the syntax is :

CREATE CLUSTERED INDEX cl_SalesHistory_SaleID ON SalesHistory(SaleID ASC, SaleDate ASC)
 WITH(DROP_EXISTING = ON, ONLINE = ON)

in above query we have made our table available/allowing for insert/update while we perform rebuild index operation on it.

ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

Solutions By:Rajesh Rolen