SQL Server ALTER INDEX DISABLE

SQL Server ALTER INDEX DISABLE is a SQL statement in the Indexes category. SQL Server: disables an index without dropping it. Disabled indexes are not maintained but can be rebuilt. The syntax is ALTER INDEX index_name ON table_name DISABLE;. It returns index disabled. A typical example: ALTER INDEX idx_employees_name ON employees DISABLE; -- Index is disabled: not maintained, not used by queries -- Metadata is preserved: can be rebuilt later -- Rebuild: ALTER INDEX idx_employees_name ON employees REBUILD; -- Check disabled indexes: SELECT name, type_desc, is_disabled FROM sys.indexes WHERE is_disabled = 1; -- Advantages vs DROP: -- - Preserves index definition and options -- - Faster to rebuild than recreate from scratch -- - DML performance… A close relative is CREATE INDEX, which creates an index on one or more columns to speed up query performance. A close relative is DROP INDEX, which removes an existing index from a table.