I agree and also use this technique to speed up table load times, but since indexes are generally maintained independently from ETL processes, I created this stored procedure that does it automatically for a given schema, table and DISABLE/ENABLE command:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.usp_IndexDisableRebuild') AND type in (N'P', N'PC'))

DROP PROCEDURE dbo.usp_IndexDisableRebuild

GO

CREATE PROC dbo.usp_IndexDisableRebuild

(

@pvchSchemaVARCHAR(256)

,@pvchTableVARCHAR(256)

,@pvchTaskVARCHAR(256)--'DISABLE' or 'REBUILD'

,@pbitDebugBIT = 0

)

AS

/*

Created By: Chris Hamam

Created: 2007-07-27

Purpose: Generic stored proc to disable/enable all non-clustered indexes on a table.

Example: EXEC dbo.usp_IndexDisableRebuild 'dbo', 'Customers', 'REBUILD'

*/

SET NOCOUNT ON

DECLARE @sql VARCHAR(max)

SET @sql = ''

SELECT @sql = @sql + 'ALTER INDEX ' + i.name + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' ' + @pvchTask + '

'

--SELECT object_name(i.object_id) tablename,*

FROM sys.indexes i

JOIN sys.objects o ON i.object_id = o.object_id

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

WHERE i.index_id > 1

AND s.name = @pvchSchema

AND o.name = @pvchTable

IF @pbitDebug = 1

PRINT @sql

ELSE

EXEC (@sql)

GO