T-SQL Alter Index in SQL Server database

T-SQL Alter Index

In SQL Server, the ALTER INDEX statement is used to modify an existing index. ALTER INDEX statement can be used to change the properties of an index, such as its fill factor or sort order, or to rebuild or reorganize the index.

Syntax


				ALTER INDEX index_name ON table_name.column_name DISABLE;	
				
ALTER INDEX index_name ON table_name REBUILD;
				
ALTER INDEX index_name ON table_name REORGANIZE;

REBUILD index

For example, to rebuild an index named "IX_Employees" on the "Employees" table, the following command can be used:

ALTER INDEX IX_Employees ON Employees REBUILD;

REORGANIZE index

To reorganize an index, the following command can be used:

ALTER INDEX IX_Employees ON Employees REORGANIZE;

To change the fill factor of an index, the following command can be used:

ALTER INDEX IX_Employees ON Employees REBUILD WITH (FILLFACTOR = 90);

It is important to note that, the ALTER INDEX statement is a blocking operation, which means that the table or view that the index belongs to will be locked for the duration of the operation. This can cause performance issues if the table or view is heavily used.