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内部是怎么实现的

　　总共是七步，详细步骤描述如下
　　

　　
CREATE TABLE dbo.Tmp_t1
　　(

　　id int NOT NULL>　　c1 char(10) NULL,
　　c2 char(10) NULL
　　)  ON [PRIMARY]
　　

　　

　　
ALTER TABLE dbo.Tmp_t1 SET (LOCK_ESCALATION = TABLE)
　　

　　


　　
SET>　　

　　

　　
IF EXISTS(SELECT * FROM dbo.t1)
　　EXEC('INSERT INTO dbo.Tmp_t1 (id, c1, c2)

　　SELECT>　　

　　


　　
SET>　　

　　

　　
DROP TABLE dbo.t1
　　

　　

　　

　　
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>　　

　　二、对现有的列删除自增属性
　　步骤一：开启SQL Server Profile（略）
　　步骤二、打开表设计的GUI界面，修改为ID列为非自增列（如下图红色框内所示），crtl+S保存一下

　　步骤三、保存完成后，停止SQL Server Profile跟踪，查看SQL Server内部是怎么实现的

　　总共是6步，详细描述如下
　　

　　
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)
　　

　　

　　
IF EXISTS(SELECT * FROM dbo.t1)
　　EXEC('INSERT INTO dbo.Tmp_t1 (id, c1, c2)

　　SELECT>　　

　　

　　
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对于现有表的列进行添加或者删除自增属性，都是通过临时表作为中转表来实现的
　　以上，如有错谬，请不吝指正，万分感谢~~