SQL Server Recovery Manager

Methods to Enable and Disable Indexes in SQL Server

Stephen West ~ Modified: January 10th, 2024 ~ SQL Transaction Log ~ 4 Minutes Reading

disable & enable sql indexes

Disable indexes feature is available in SQL Server 2005 and all the later versions to prevent user access to the index. In Microsoft SQL Server 2000 and earlier versions of the SQL Server only have a DROP index and RE-CREATE index option. Disable indexes are very useful feature when we need to recognize whether the indexes are useful or not. The index definition remains in metadata when we are disabling an index and index statistics are kept on non-cluster indexes. Let’s continue understand the enable and disable index in SQL server task.

Table of Content

However, if you are disabling a clustered index on the table, even then the data still remains on the table, but the table won’t be available for users access. The data of the cluster index will be unavailable for any DML (data manipulation language) operations until the index is dropped or rebuild.

In this blog post I will show you how to disable and enable the index by using SQL Server Management Studio or Transact-SQL

User Queries to Disable and Enable Index in SQL Server

Before we move further to find out the ways to disable index SQL Server & then enable index SQL Server, let’s have a look at the user queries. This can help us get a clear understanding of the entire task.

disable indexes

Now, that we have gone through the user queries, let’s proceed to know all the ways to disable & enable the indexes.

SQL Server Disable Index Using Transact-SQL

In Object Explorer, click New Query and run the following query on AdventureWorks database to disable IX_Address_StateProvinceID index on Person.Address table

Disable index using T-SQL

SQL Server Disable Index with Management Studio (SSMS)

In Object Explorer expands the database by clicking on the plus sign until you get the IX_Address_StateProvinceID index. Then right click on the IX_Address_StateProvinceID and from the drop down list select Disable. Verify the Disable Indexes and click OK.

Disable indexes using ssms

You can also disable an index form Index Properties dialog box

Click Options under Select a page, column and uncheck Use index option as shown below

Disable index from index properties

Enable an Index using Transact-SQL

There are two different T-SQL statements to enable indexes

  1. ALTER INDEX REBUILD Statement
  2. CREATE INDEX WITH DROP_EXISTING Statement

Enable Index using ALTER INDEX REBUILD Statement

Enable index using ALTER INDEX REBUILD

Enable Index using CREATE INDEX WITH DROP_EXISTING Statement

Enable index using CREATE INDEX WITH DROP_EXISTING

Enable Index using SQL Server Management Studio (SSMS)

In Object Explorer expands the database by clicking on the plus sign until you get the IX_Address_StateProvinceID index. Then right click on the IX_Address_StateProvinceID and from the drop down list select Rebuild. Click OK on Rebuild Indexes dialog box, to enable the index

enable index using ssms

You can also enable an index form Index Properties dialog box

Click Options under Select a page, column and check Use index option as shown below

enable index using index properties

Tackle Corrupt Indexes in SQL Server

There are often times when users face corruption in SQL Server objects. Evidently, this corruption can even occur in the SQL Server indexes. Now, users must know the right way to get rid these corruption issue. Therefore, the recovery software for SQL Server is the right choice for them. Download the software & get the perfect solution to tackle all sorts of corruption issues with SQL indexes & other objects.

Download Now Purchase Now

Conclusion

Now after going through all the methods, we are hoping that users are aware of the right method to execute their SQL server disable index as well as enable index task. make sure to not miss any of the steps mentioned above to alter index SQL server in a safe manner.