For the past several editions, SQL Books On-Line (BOL) has helpfully included a script to rebuild or defragment (since 2000) an index. Being Microsoft, this script is NOT located under the reindex or defragmentation topic, it is included in the fragmentation analysis section. For SQL 2000, this is DBCC SHOWCONTIG. For SQL 2005, they rewrote it to use the new system views and stashed it under sys.dm_db_physical_stats. It also uses the new ALTER INDEX command rather than the older DBCC DBREINDEX or DBCC INDEXDEFRAG command. However, this script does not allow you to take advantage of online indexing

My first attempt to modify the script consisted of dropping the defragmentation option and simply adding a WITH ONLINE = ON line to the script. As with most quick and dirty mods, this failed miserably. I soon discovered that some indexes cannot be rebuilt online. There are two conditions that will prevent online reindexing, both of which involve BLOB data types:

You cannot online reindex a non-clustered index that contains a BLOB column.

You cannot online reindex a clustered index of a table that contains a BLOB column.

Pretty straightforward, or so it seems. The code to identify BLOB columns from an index is a bit tricky, but not too impossible. I used actual data type names rather than the internal data type IDs to make the code more readable. I also added a filter condition to remove "trivial" sized indexes. These are typically allocated into mixed extents and do not respond to defragmentation. There is no real harm in including them, but they clutter up the result set when troubleshooting. I kept tweaking the number until I got it as low as possible without an index reappearing in subsequent runs. The test platform was a Microsoft CRM implementation. For those unfamiliar with the table design of MSCRM, it uses GUIDs as primary keys and accepts the default of clustering the primary key. This makes for a nicely fragmented database to test, especially if there is a lot of insert and delete activity.

So without further ado, here is the modified BOL code that allows for scripting online index rebuilds.

-- Adapted from BOL script by Geoff N. Hiten.

USE <Your Database name here>

GO

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @HasBlobColumn int;

DECLARE @MaxFragmentation int

DECLARE @TrivialPageCount int

-- Tuning constants

SET @MAxFragmentation = 10

SET @TrivialPageCount = 12

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > @MaxFragmentation -- arbitrary threshold. YMMV

    AND index_id > 0 -- cannot defrag a heap

    and page_count > @TrivialPageCount -- ignore trivial sized indexes

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

        Set @HasBlobColumn = 0 -- reinitialize

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

            FROM sys.objects AS o

            JOIN sys.schemas as s ON s.schema_id = o.schema_id

            WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

            FROM sys.indexes

            WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

            FROM sys.partitions

            WHERE object_id = @objectid AND index_id = @indexid;

        -- Check for BLOB columns

        if @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts

            Select @HasBlobColumn = case when max(so.object_ID) IS NULL then 0 else 1 end

                From sys.objects SO

                inner join sys.columns SC

                    on SO.Object_id = SC.object_id

                inner join sys.types ST

                    on SC.system_type_id = ST.system_type_id

                    and ST.name in ('text', 'ntext', 'image', 'varchar(max)', 'nvarchar(max)', 'varbinary(max)', 'xml')

                where SO.Object_ID = @objectID

            else -- nonclustered. Only need to check if indexed column is a BLOB

            Select @HasBlobColumn = case when max(so.object_ID) IS NULL then 0 else 1 end

                from sys.objects SO

                inner join sys.index_columns SIC

                    on SO.Object_ID = SIC.object_id

                inner join sys.Indexes SI

                    on SO.Object_ID = SI.Object_ID

                    and SIC.index_id = SI.index_id

                inner join sys.columns SC

                    on SO.Object_id = SC.object_id

                    and SIC.Column_id = SC.column_id

                inner join sys.types ST

                    on SC.system_type_id = ST.system_type_id

                    and ST.name in ('text', 'ntext', 'image', 'varchar(max)', 'nvarchar(max)', 'varbinary(max)', 'xml')

                where SO.Object_ID = @objectID

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

        if @HasBlobColumn = 1

            Set @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '

        else

            Set @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

PRINT N'Executing: ' + @command + ' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);

        EXEC (@command)

-- PRINT N'Executing: ' + @command;

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO