SQL Server Rename Index

Learning Objective

The objective of this SQL Server tutorial is to teach you how to rename an index associated with a table or view.

Introduction to SQL Server Rename Index

Renaming an index changes the name of an index and gives it the new name. It does not affect the functionality of the index in any way. It does not rebuild the index. The new name must be unique as a table cannot have two indexes with the same name. However, two different tables can have indexes having same name.

Operation

An index is renamed using a system stored procedure called sp_rename. The sp_rename stored procedure allows us to rename any user created object in the database including tables, indexes, views and including even the database (though that is not the standard way of renaming a database and will not be supported by Microsoft in future).

SQL Server Rename Index Syntax

The basic syntax of the command to rename an index using is as follows. The command does not have any variation for the different index types.

EXEC SP_RENAME
'old_index_name',
'new_index_name',
'INDEX';

In this syntax,

  • SP_RENAME – name of the system stored procedure which is executed.
  • INDEX – telling SQL server that the object being referred to for renaming is an index.

SQL Server Rename Index Examples

We have a table in our database called students which contains basic student information. The table has two indexes defined on it by the names PK_students and ix_stream_index as can be seen below in the screenshot from the SSMS GUI and the output of the T-SQL command which lists the index names for a table.

SQL Server Rename Index

Using T-SQL command

EXEC SP_HELPINDEX students;
index_nameindex_descriptionindex_keys
ix_stream_indexnonclustered located on PRIMARYstream
PK_studentsclustered, unique, primary key located on PRIMARYrollno

1) Renaming the Index

Using T-SQL

We will now rename the index ix_stream_index by executing the sp_rename command as below. The new name given is ix_student_stream.

EXEC SP_RENAME
'ix_stream_index',
'ix_student_stream',
'INDEX';

The same operation can also be performed from the (SQL Server Management Studio) SSMS GUI by right clicking the index in Object Explorer and selecting the Rename option from the shortcut menu. Then type in the new name in place of the old name and press enter or click outside and the rename will be complete. The below figures show the steps.

2) Checking the Rename

After the rename we can check and verify the new name using T-SQL or in the SSMS GU which will both show the new name. The below shows the same.

Using T-SQL command

EXEC SP_HELPINDEX students;
index_nameindex_descriptionindex_keys
ix_student_streamnonclustered located on PRIMARYstream
PK_studentsclustered located on PRIMARYrollno

Advertisement