Online Indexing is a new feature available in SQL Server 2005.
Many times people ask me what is Online Indexing or Online Operations? And every time I answer that it is not related to Web or Internet.
Online Operation means the database is available to all the users and is fully operational, and the processes which are participating in the online operations are performed concurrently with the update and the query operations.
So in online index DDL Operations like (create, rebuild, dropping) do not require exclusive locks on the underlying data and the associated indexes.
This was not possible in previous versions of SQL Server. When one used to perform the DDL Operations offline then these operations held exclusive locks on the underlying table and the associate indexes. So in the past, the indexing operations were usually performed during off-peak hours. This is the reason of time-outs and lock-outs when the index operations were performed in the SQL Server. The default value of ONLINE option is OFF.
In the following example all indexes on the Product table in the AdventureWorks database are rebuild online. The example is taken from BOL.
ALTER INDEX ALL on Production.Product
REBUILD WITH (Online = ON);
Online Index operations can be divided into three steps:
- Preparation: Original Index structures known as source index are copied at another place.
- Build: This is the longest phase of all. In this phase the creation, dropping or rebuilding of indexes take place and generates the new required index known as target index.
- Final: The new index structure created called target index replaces the original source index.