SQL SERVER – Disabling Indexes – Non Clustered Indexes

Recently, I came across a fantastic T-SQL script that offers an additional feature for changing the recovery mode while enabling and disabling indexes. What impressed me most about this script is its ability to change the SQL Server recovery mode to Simple during index operations, significantly speeding up the process. This stored procedure is self-documented, so I won’t elaborate much on its details. Let us learn how to disable and enable indexes efficiently. Let us learn about disabling indexes.

Stored Procedure USP_DisableEnableNonClusteredIndexes can be downloaded from here.

Execute SP using the following script.

-- sample to Execute
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',1 -- DISABLE
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'N',2 -- REBUILD/ENABLE with out Changing Recovery model.
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', 'Y',2 -- REBUILD/ENABLE with Change Recovery model.
-- or
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS',NULL, 1 -- DISABLE
EXEC USP_DisableEnableNonClusteredIndexes 'ADVENTUREWORKS', NULL,2 -- REBUILD/ENABLE with out Changing Recovery model.

Here are few additional blog posts related to index, which you should read about:

Introduction to Force Index Query Hints – Index Hint

For this article, I will start with a disclaimer instead of having it at the end of the article.

“SQL Server query optimizer selects the best execution plan for a query, it is recommended to use query hints by experienced developers and database administrators in case of special circumstances.”

SQL SERVER – Introduction to Force Index Query Hints – Index Hint – Part2

I just received an email from one of my regular readers that, are there any other methods for the same as it will be difficult to read the syntax of the joint.Yes, there is an alternate way to do the same using OPTION clause, however, as an OPTION clause is specified at the end of the query we have to specify which table the index hint is put on.

Reference: Pinal Dave (https://blog.sqlauthority.com)