From SQL Server 2005 onwards, the indexes can be re-indexed ONLINE. This ensures that the underlying tables and indexes are available for other users when the re-indexing is taking place. Also Standard and Workgroup editions of SQL
The limitation for ONLINE Re-indexing is that it cannot be run on tables which have Large Object Data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), xml). The Alter Index documentation on MSDN has more details on this.
Yesterday, I responded to a question which read “How to find out LOB data types in a table?”. There are many ways to find it out, here is the one which I use frequently.
SELECT a.name as [ColumnName],B.name as [Table Name] FROM SYS.COLUMNS A JOIN SYS.OBJECTS B ON A.object_id = B.object_id WHERE A.system_type_id in (35,34,241,99) AND B.type ='U' AND B.name <>'dtproperties' UNION SELECT a.name, B.NAME FROM SYS.COLUMNS A JOIN SYS.OBJECTS B ON A.object_id = B.object_id WHERE A.system_type_id IN (167,231,165) AND A.max_length = -1 AND B.type ='U' AND B.name <>'dtproperties'
With this script, the LOB data type columns can be identified and is useful while trying to reindex selected tables online.