当我需要重命名一个表并因此重命名它的主键(以及所有其他约束)时,我使用以下简单的示例脚本:
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
我所看到的是,当我的表超过一百万行或任何大小时,或者服务器很忙时,人们倾向于重命名表,然后删除并重新创建主键,据我所知工作量很大,资源非常紧张。
例如(与前面示例无关的名称):
/* 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
)
现在我不知道 sp_rename 在幕后做了什么,我还没有机会在繁忙的实时服务器上测试它。
问题是:
在繁忙的实时服务器上,sp_rename
重命名表的主键会比alter table drop and add constraint
?
sp_rename
是一个系统存储过程,它更新适用对象名称的适当元数据,如Microsoft Learn 文档中所述。它通过未记录的内部方法调用来执行此操作,以在比 T-SQL 更低级别的代码中进行更改。具体来说,如果你破解打开sp_rename
,可以看到具体内部方法调用的代码是EXEC %%Object(ID = @objid).SetName(Name = @newname)
。更改表以删除并重新创建主键约束,当它也是表上的聚集索引时(如您的示例所示),不仅仅是对该表的元数据更改:
聚簇索引定义表本身行的实际逻辑排序。删除主键约束并引入聚簇索引会导致表从有序数据结构(B 树)切换到无序堆数据结构。然后重新添加主键约束,集群,导致相反的切换发生,使得堆需要转换回 B 树。
此外,表上的所有非聚集索引都包含存储在其中的聚集索引键。当您删除并重新创建聚簇索引时,该表上的每个非聚簇索引都需要相应地更新,对于这两个操作也是如此。
由于
sp_rename
主要进行一些错误检查和元数据更改,因此在大多数情况下它应该更快。在我的测试数据库中有一个小表的(少于全部)示例表明,通过删除和重新创建主键聚集索引来重命名表所花费的时间大约是它使用时的5 倍sp_rename
:删除并重新创建主键聚簇索引 (注意:为了更公平,
我将解析和编译时间抛到窗外,只比较执行时间。)
使用![sp_重命名](https://i.stack.imgur.com/ZJqcS.png)
sp_rename
作为参考,您可以通过在运行测试之前运行来自己分析时间统计信息。
SET TIME STATISTICS ON
Richie Rump 的StatisticsParser.com有助于格式化结果以进行比较。