Just a day ago, during one interview question of Online Indexing come up. I really enjoy discussing this issue as I was talking with candidate who was very smart.
Following two questions were discussed.
1) What is Online Index Rebuild Operation?
Online operation means when online operations are happening the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to database.
Read about this in-depth in my previous article SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop
2) What are the limitation of the Online Index Rebuild Operation?
Following indexes can not be build online. We were able to come up with first four kind of the indexes. I researched book online before I wrote this article have listed all the six exceptions of index rebuild operation.
- Disabled indexes
- XML indexes
- Indexes on local temp tables
- Partitioned indexes
- Clustered indexes if the underlying table contains LOB data types
- Nonclustered indexes that are defined with LOB data type columns
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com), BOL – Alter Index
