SQL Server: Modify or Delete Indexes

Here you will learn how to modify or delete indexes in SQL Server.

Modify Index

To add, remove, or change the position of an index column, you must drop and recreate the index. However, you can set several options on the index using ALTER INDEX statement.

The following query modifies the NCI_Employee_Email index on the Employee table to set the IGNORE_DUP_KEY to ON. It specifies the error response when a duplicate key value is inserted into a unique index. The default value is OFF.

Example:

ALTER INDEX NCI_Employee_Email ON dbo.Employee
SET (IGNORE_DUP_KEY = ON)

Modify Indix using SSMS

Step 1: Open SSMS. Connect to the database.

Step 2: In Object Explorer, expand the table where the index you want to modify belongs.

Step 3: Expand the Indexes folder and right-click on an index which you want to modified and click Properties.

Step 4: In the Index Properties dialog box, under General tab, make the desired changes like Adding/removing columns from the index key or change the setting of an Index option.

Delete Indexes

Use the DROP INDEX statement to delete existing clustered and non-clustered indexes.

The following SQL command deletes the clustered index CIX_EmpDetails_EmpId on the EmployeeDetails table.

Example: Delete Clustered Index

DROP INDEX CIX_EmpDetails_EmpId
ON dbo.EmployeeDetails

The following SQL statement deletes the non-clustered index NCI_Employee_Email on the Employee table.

Example: Delete Non-Clustered Index

DROP INDEX NCI_Employee_Email
ON dbo.Employee;

Note: Indexes created as a result of Primary Key or UniqueKey constraints cannot be deleted using the above methods. The constraint has to be deleted to delete the index.

Delete Index using SSMS

Step 1: Open SSMS. Connect to the database.

Step 2: In Object Explorer, expand the table having the index you want to delete.

Step 3: Expand the Indexes folder and right-click on the index to be deleted and select Delete in the context menu.

Step 4: In the Delete Object dialog box, verify that the correct index is selected and click OK.

Delete Object Using Table Designer

Step 1: In the Object Explorer in SSMS, right-click on the table where you want to delete an index and click Design.

Step 2: On the Table Designer menu, click on Indexes/Keys option.

Step 3: In the Indexes/Keys dialog box, select the index you want to delete.

Step 4: Click Delete.

Step 5: Click Close.

Step 6: On the File menu, select Save table name

Thus, you can modify or delete indexes.