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.
Using T-SQL command
EXEC SP_HELPINDEX students;
| index_name | index_description | index_keys |
| ix_stream_index | nonclustered located on PRIMARY | stream |
| PK_students | clustered, unique, primary key located on PRIMARY | rollno |
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_name | index_description | index_keys |
| ix_student_stream | nonclustered located on PRIMARY | stream |
| PK_students | clustered located on PRIMARY | rollno |
