Renaming Indexes with 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. You can also read how to rename table in SQL.

CodingSight - Renaming Indexes with sp_rename Procedure

SQL Server sp_rename Basics

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.

  1. new_name specifies the new name of the object that must be the one-part identifier.
  2. 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 Index with SSMS

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.
Rename indexes using SSMS

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

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.

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:

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:

The output of the query to verify that the index has been renamed

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.

SQL Server sp_rename Stored Procedure to Rename Index

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:

The output of the query to verify the index renaming

Important Notes about Rename Index

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

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

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 output of the query to review the execution plan of the query using the PK_tblStudent index

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

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

Leave a Reply

Your email address will not be published. Required fields are marked *