SQL Server - Alter Index

In previous post, we have learnt about Index and its types. In this post, we will learn how index can be changed. ALTER INDEX statement is used to change the definition of index or its status – enabled or disabled. First, we see why and how index can be disabled on a table or view.

Why Disable Index

Disabling an index can be useful in scenarios where it is required to test the performance impact of running queries without a specific index. Disabling an index may lead to suboptimal query performance for queries that could benefit from the index. Therefore disabling index must be done after due consideration.

How to Disable Index

In SQL Server, you can disable an index temporarily using the following syntax:

ALTER INDEX [IndexName] ON TableName DISABLE;

Here, [IndexName] is the name of the index you want to disable, and TableName is the name of the table containing the index. This statement marks the index as disabled, and SQL Server will not consider it during query optimization.

Alternatively, you can disable Index using GUI. Right click the Index in the object explorer. Click Disable menu item.

How to Enable Index

ALTER INDEX [IndexName] ON TableName REBUILD;

This statement rebuilds the index, making it active again. Alternatively, you can use the ENABLE option directly:

ALTER INDEX [IndexName] ON TableName ENABLE;

Disable vs Drop Index

When an index is disabled, its definition is retained, and it can be re-enabled later. If you drop an index using the DROP INDEX statement, you would need to recreate it if you want to use it again. When index is dropped resource is released.

Since index affects the query performance, dropping or disabling or altering indexes, especially in a production environment must be done after due consideration.

In the next post we will learn about Computed Index in SQL Server.