Automatically Drop and Recreate current indexes

etl, sql, sql-server-2008

Solution

I have script that I use to query the system tables to capture all non-clustered indexes and disable then rebuild upon completion. The below is for use on standard edition, if you are on enterprise I would add the `ONLINE` option.

Disable

DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT 
    @sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] DISABLE; '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'

EXEC (@sql)

Rebuild

DECLARE @sql AS VARCHAR(MAX);
SET @sql = '';
SELECT 
    @sql = @sql + 'ALTER INDEX [' + i.name + '] ON [' + o.name + '] REBUILD WITH (FILLFACTOR = 80); '
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE i.type_desc = 'NONCLUSTERED'
AND o.type_desc = 'USER_TABLE'

EXEC (@sql);

I like this method as it is very customizable as you can exclude/include certain tables based on the conditions as well as avoiding a cursor. Also you can change the `EXEC` to a `PRINT` and see the code that will execute and manually run it.

Condition to exclude a table

AND o.name NOT IN ('tblTest','tblTest1');

Problem

I'm working on providing a method to allow for bulk updating our tables ( greater than 1M new or updated rows per update) and was interested in dropping the current indexes and recreating them after the updates. I was wondering if anyone has a script to provide loose coupling of these operations so that if the indexes change over time, the update process does not change. It seems like this is one of those things that the community has already probably solved.

Original source