Just a day before I received the wonderful T-SQL Script from SQL Server Expert Imran Mohammed. What I am really impressed with this script is an additional feature of changing recovery mode when SQL Server is enabling and disabling Index. When index is enabled changing SQL Server Recovery mode to simple makes the whole operation faster. This stored procedure is self documented so I am not writing much about it. Let us learn about how to disable index.
Stored Procedure USP_DisableEnableNonClusteredIndexes can be downloaded from here.
Execute SP using 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:
This article, I will start with 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.”
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)