best and quickest way to rename the primary key of a table

Question

when I need to rename a table and consequently its primary key (and all other constraints) as well I use the following simple example script:

if object_id('dbo.Radhe1',N'U') is not null
   drop table dbo.radhe1

create table dbo.Radhe1 (
    id int identity(-1008,-1) not null,
    name nvarchar(50) null
  , constraint pk_Radhe1 primary key clustered (Id)
);

insert into dbo.Radhe1(name)values('Krishna')
go 1008

select count(*) from dbo.radhe1


exec sp_rename N'dbo.Radhe1.pk_Radhe1', N'pk__Radhe_to_be_removed';

exec sp_rename N'dbo.Radhe1', N'Radhe1_to_be_removed'

select count(*) from dbo.Radhe1_to_be_removed

what I have seen is that when I table is over a million rows or any substantial size, or the server is a busy one, people tend to do is rename the table, then drop and re-create the primary key, which I understand to be a lot of work, very intense on resources.

for example (names unrelated to the previous example):

/* rename existing tables */
EXEC sp_rename 'dbo.DocumentDetailSearchTags', 'dbo.DocumentDetailSearchTags_TOBEDROPPED'
EXEC sp_rename 'dbo.SearchTags', 'dbo.SearchTags_TOBEDROPPED'

/* update pk constraint names */
ALTER TABLE dbo.DocumentDetailSearchTags_TOBEDROPPED
DROP CONSTRAINT PK_DocumentDetailSearchTags
ALTER TABLE dbo.DocumentDetailSearchTags_TOBEDROPPED
ADD CONSTRAINT PK_DocumentDetailSearchTags_TOBEDROPPED PRIMARY KEY CLUSTERED 
(
  [documentDetailId] ASC,
  [searchTagId] ASC
)

Now I don't know what would the sp_rename be doing behind the scenes and I havent had the chance to test this on a busy live server.

the question is:

on a busy live server, sp_rename to rename the primary key of a table would work better than alter table drop and add constraint ?

Answer 1

sp_rename is a system stored procedure that updates the appropriate meta-data for the name of an applicable object, as discussed in the Microsoft Learn docs. It does this via undocumented internal method calls to make that change in lower level code than T-SQL. Specifically, if you crack open sp_rename, you can see the code for the specific internal method call is EXEC %%Object(ID = @objid).SetName(Name = @newname).

Altering a table to drop and recreate the primary key constraint, when it is also the clustered index on the table (as in your example), is more than a meta-data change to that table:

1. The clustered index defines the actual logical sort of the rows of the table itself. Dropping the primary key constraint and ergo the clustered index causes the table to be switched from an ordered data structure, a B-Tree, to an unordered Heap data structured. Then re-adding the primary key constraint, clustered, causes the opposing switch to happen such that the Heap needs to be converted back into a B-Tree.

2. Additionally, all nonclustered indexes on a table contain the clustered index key stored in them. When you drop and recreate the clustered index, every nonclustered index on that table needs to be updated accordingly, for both actions, as well.

Since sp_rename mostly does some error checking and meta-data changes, it should be faster on most occasions. A (less than be-all end-all) example with a small table in my test database shows that the time spent renaming the table via dropping and recreating the primary key clustered index took about 5x as long as it does using sp_rename:

Dropping and Recreating the Primary Key Clustered Index (Note: I'm throwing parse and compile time out the window to be more fair, and only comparing execution time.)

Using sp_rename

For reference, you can profile the time statistics yourself by running SET TIME STATISTICS ON prior to running your tests. Richie Rump's StatisticsParser.com is helpful for formatting the results for comparison.