SQL with Manoj

 
There are times when you need to DISABLE all Indexes of a table, like when there is a need to INSERT/UPDATE huge records in a table. As you have to INSERT huge records in a table the whole INSERT process could take more time to maintain/process Indexes on that table. Thus before inserting records its good to DISABLE all Non-Clustered indexes and post processing Re-Enable them.

USE [AdventureWorks2014]
GO

-- Disable Index
-- Syntax: ALTER INDEX [idx_name] ON [table_name] DISABLE;
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] DISABLE;
GO

-- Enable Index
-- Syntax: ALTER INDEX [idx_name] ON [table_name] REBUILD;
ALTER INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] REBUILD;
GO

Please note: to Enable you need to use REBUILD option, there is no ENABLE option just like DISABLE in above DDL statements.
 

–> Generate Queries of ALTER DDL scripts to:

– Disable all Indexes:

SELECT 
	o.name, 
	'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' 
		+ QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
		+ '.' + QUOTENAME(o.name) + ' DISABLE;'
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
AND o.name = 'Person'

– Enable all Indexes:

SELECT 
	o.name, 
	'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' 
		+ QUOTENAME(SCHEMA_NAME(o.[schema_id])) 
		+ '.' + QUOTENAME(o.name) + ' REBUILD;'
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
WHERE o.is_ms_shipped = 0
AND i.index_id >= 1
AND o.name = 'Person'