项目需求:线上有一张表,数据类型为int类型,现在由于项目变更,需要这一列添加自增属性,而且,为了保证能尽快完成,希望使用脚本来实现,而不是在表设计中通过GUI窗口来实现。
问题来了:SQL Server有类似于alter table的语法来直接修改表的列为自增列的吗?答案是:没有!那么,表设计中是如何实现的呢?
创建一张测试表t1,然后使用SQL Server Profile来看看内部是怎么实现的。
一、对现有的列添加自增属性
步骤一:创建测试表t1
--表若存在,就删除
if(object_id('t1') is not null)
begin
drop table t1 ;
end ; --创建测试表
create table t1(id int, c1 char(10), c2 char(10)) ; --插入测试数据
insert into t1(id, c1, c2) values(1,'aaaaaaaaaa','bbb'),(10,'aaaaaaaaaa','bbb'),(100,'aaaaaaaaaa','bbb'),(1000,'aaaaaaaaaa','bbb');
步骤二:开启SQL Server Profile(略)
步骤三:打开表设计的GUI界面,修改为ID列为自增列(如下图红色框内所示),crtl+S保存一下
步骤四:保存完成后,停止SQL Server Profile跟踪,查看SQL Server内部是怎么实现的
总共是七步,详细步骤描述如下
--1、创建与原表表结构一致的临时表,并且在列上添加了自增属性
CREATE TABLE dbo.Tmp_t1
(
id int NOT NULL IDENTITY (10, 1),
c1 char(10) NULL,
c2 char(10) NULL
) ON [PRIMARY] --2、把新增临时表的锁升级为表锁
ALTER TABLE dbo.Tmp_t1 SET (LOCK_ESCALATION = TABLE) --3、设置新增临时表的自增列为可插入状态
SET IDENTITY_INSERT dbo.Tmp_t1 ON --4、把原表中的数据插入到临时表里
IF EXISTS(SELECT * FROM dbo.t1)
EXEC('INSERT INTO dbo.Tmp_t1 (id, c1, c2)
SELECT id, c1, c2 FROM dbo.t1 WITH (HOLDLOCK TABLOCKX)') --5、设置新增临时表的自增列为不可插入状态
SET IDENTITY_INSERT dbo.Tmp_t1 OFF --6、删除原表
DROP TABLE dbo.t1 --7、把临时表的表名修改为跟原表一致
EXECUTE sp_rename N'dbo.Tmp_t1', N't1', 'OBJECT'
可见,SQL Server内部也是通过使用临时表作为中转来实现把列修改为自增列的,所以,如果真的需要用脚本而非GUI来实现修改为自增列,可以参考以上的7个步骤。
注意:在设计中是修改了标识种子为10,所以在创建临时表Tmp_t1的时候出现了IDENTITY(10,1),如果没有修改标识种子,默认的是IDENTITY(1,1),可以在修改完成后使用以下语句进行修改
--修改自增列的标识种子
DBCC CHECKIDENT('t1', reseed, 100) ; --查看自增列的当前值
SELECT IDENT_CURRENT('t1')
二、对现有的列删除自增属性
步骤一:开启SQL Server Profile(略)
步骤二、打开表设计的GUI界面,修改为ID列为非自增列(如下图红色框内所示),crtl+S保存一下
步骤三、保存完成后,停止SQL Server Profile跟踪,查看SQL Server内部是怎么实现的
总共是6步,详细描述如下
--创建临时表dbo.Tmp_t1
CREATE TABLE dbo.Tmp_t1
(
id int NOT NULL,
c1 varchar(20) NULL,
c2 varchar(20) NULL
) ON [PRIMARY] --锁定临时表,锁级别为表锁
ALTER TABLE dbo.Tmp_t1 SET (LOCK_ESCALATION = TABLE) --把原来的表的数据插入到临时表dbo.Tmp_t1
IF EXISTS(SELECT * FROM dbo.t1)
EXEC('INSERT INTO dbo.Tmp_t1 (id, c1, c2)
SELECT id, c1, c2 FROM dbo.t1 WITH (HOLDLOCK TABLOCKX)') --删除原表
DROP TABLE dbo.t1 --将临时表改名为原表
EXECUTE sp_rename N'dbo.Tmp_t1', N't1', 'OBJECT' --添加索引
ALTER TABLE dbo.t1 ADD CONSTRAINT
PK__t1__3213E83F7F60ED59 PRIMARY KEY CLUSTERED
(
id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
三、总结
从以上的跟踪结果可以看出,在SQL Server对于现有表的列进行添加或者删除自增属性,都是通过临时表作为中转表来实现的
以上,如有错谬,请不吝指正,万分感谢~~