SQL Server现有表上自增属性增删原理研究

　　项目需求：线上有一张表，数据类型为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对于现有表的列进行添加或者删除自增属性，都是通过临时表作为中转表来实现的

 

　　以上，如有错谬，请不吝指正，万分感谢~~