SQL Server: Renaming Indexes using the sp_rename Procedure

Not so long ago, I was working on a project where we needed to change the data type of a table. The table had millions of rows, and we decided to create a separate table using the existing table definition and insert the rows into that table. After exporting data into the new table, we renamed the old table, constraint, and indexes using the sp_rename stored procedure.

The current article is dedicated to the ways of renaming indexes. 

Getting Started with Renaming Indexes in SQL Server

We can rename the index with one of the following methods:

1. Using SQL Server Management Studio.
2. Using the sp_rename stored procedure.

First, let us understand the basics.

The sp_rename procedure is used to rename tables, table columns, index names, and common language runtime user-defined types.

The syntax of the procedure is as follows:

Exec sp_rename ‘object_name’, ‘new_name’, ‘object_type’

1. object_name specifies the name of the object you want to rename.
   1. If you are renaming the table, the format of the object_name parameter can be [table_name] or [schema_name].[table_name].
   2. If you are renaming the column name of the table, the format of the object_name parameter should be [table_name].[column_name].
   3. If you are renaming the index name of the table, the format of the object_name parameter should be [schema_name].[table_name].[index_name]. Or it can be [table_name].[index_name].

Note: If you are using a qualified object name, you must specify the quotation mark.

2. new_name specifies the new name of the object that must be the one-part identifier.
3. object_type specifies the type of object you want to rename (an optional parameter). The value of the object_type should be any of the following:
   1. COLUMN
   2. DATABASE
   3. INDEX
   4. OBJECT
   5. STATISTICS

Demo Database Setup

For our explanation and test purposes, I have prepared a demo setup. There is the database named StudentDB, where I have created a table named tblStudent.

The query to create the database:

Create database [StudentDB]

The query to create the table:

USE [StudentDB]
GO
CREATE TABLE [dbo].[tblStudents](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[student_name] [varchar](250) NOT NULL,
	[student_code] [varchar](5) NOT NULL,
	[student_grade] [char](2) NOT NULL,
	[SchoolID] [int] NOT NULL,
 CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED([ID] ASC))

I have also created the index named INDX_tblStudents_Std_Code on the tblStudent table:

USE [StudentDB]
GO
CREATE NONCLUSTERED INDEX [INDX_tblStudents_Std_Code] ON [dbo].[tblStudents]
([student_code] ASC)

The following query inserts the data into the table:

insert into [tblStudents]
(student_name,student_code,student_grade,SchoolID)
values
('Nisarg Upadhyay','ST001','A',1),
('Dixit Upadhyay','ST003','A',1),
('Bharti Upadhyay','ST004','C',2),
('Nimesh Patel','ST005','C',2),
('Raghav Dave','ST006','A',1)

Now, let us see how we rename the PK_tblStudent index using SQL Server Management Studio.

Rename indexes using SQL Server Management Studio

First, let us rename the PK_tblStudent index.

1. Launch SQL Server Management Studio and connect to the database engine.
2. Expand the StudentDB database > Tables > tblStudents > Indexes
3. Right-click on the PK_tblStudent index > select Rename from the menu, or single-click on the index name.

The index becomes editable. Specify the new name of the index and hit Enter.

Before changing the index name, SSMS will show the following warning. Click Yes.

The index name has been changed, and the changes are applied immediately.

If the index name isn’t updated, refresh the Indexes folder:

Run the following query to verify that the index has been renamed:

select object_name(object_id) [table name],name [Index Name], type_desc [Index Type] from sys.indexes where object_id=object_id('tblStudents')

The output:

As you can see, the index name has been changed, and SSMS proved efficient for this purpose.

Now, let us understand how we can rename the index using the sp_rename stored procedure.

Rename Indexes Using the sp_rename Stored Procedure

We are going to rename the index named INDX_tblStudents_Std_Code to INDX_tblStudents_Student_Code.

Our sp_rename stored procedure will work in the following code:

exec sp_rename 'dbo.tblStudents.INDX_tblStudents_Std_Code','INDX_tblStudents_Student_Code'

Run the query to verify the index renaming:

select object_name(object_id) [table name],name [Index Name], type_desc [Index Type] from sys.indexes where object_id=object_id('tblStudents')

The output:

Important Notes

Security and Permission

To rename the index, you must have ALTER permission on the index.

How Statistics changes

When we rename the index, the statistics associated with the index are renamed automatically. Let us view the statistics associated with the Primary Key.

Open SQL Server Management Studio > Expand the necessary database > tblStudents table > Statistics

As you can see, the statistics have been renamed.

How Query execution plan changes

When we rename the index, the metadata of the query execution plan is changed too automatically. To make it clearer, let us review the execution plan of the query using the PK_tblStudent index.

Run the following SELECT statement:

Select * from [tblStudents] where ID=’5’

The output:

The index name is changed in the execution plan.

Summary

Therefore, we’ve learned about the sp_rename stored procedure syntax and usage. Moreover, we’ve identified and tried two ways of renaming indexes on the table. Now we can do it both with SSMS or with the dedicated stored procedure. We’ve also clarified the impacts of changing the indexes names on the statistics and query execution plans. What is more, finding and fixing index fragmentation issues can be accomplished with the help of dbForge Index Manager. I hope that this article with its practical tips will prove helpful for your work.

Related articles

Different Ways to Rename SQL Server Tables

Tags: indexes, sp_rename, sql rename, sql server Last modified: April 24, 2023