在我们的业务中，我们经常碰到去掉表的identity属性的需求。但是sql并没有提供可以直接去掉该属性的sql语句。但是其实我们自己可以通过三种方式来实现。
第一种：通过界面操作
将以上图中的yes改为no ，然后点击保存，这里需要注意一个问题，sql server好像默认是不允许这样改，这里我们就需要修改一个选项
需要将以上红色框内的选项去掉，这样就可以保存修改，并且保存修改所使用的脚本。
第二种方式，其实就跟上面类似，只是我们可以按照它的原理通过脚本实现。
1 use Test 2 go 3 if OBJECT_ID('dbo.RemoveIdentiyTest') is not null 4 drop table dbo.RemoveIdentiyTest 5 go 6 --新建一个测试表 7 create table dbo.RemoveIdentiyTest 8 ( 9 id int identity not null,10 col1 varchar(10) not null constraint DF_RemoveIdentiyTest_col1 default 'aaa',11 col2 varchar(10) null,12 col3 int null,13 col4 int null,14 col5 char(10) null15 constraint PK_RemoveIdentiyTest primary key clustered16 (17 id asc18 )19 ) ON [PRIMARY]20 go21 create nonclustered index IX_RemoveIdentiyTest_col3_col4 on dbo.RemoveIdentiyTest22 (23 col3,col424 )with(fillfactor=80) on [primary]25 go26 create nonclustered index IX_RemoveIdentiyTest_col5 on dbo.RemoveIdentiyTest27 (28 col529 )with(fillfactor=80)on [primary]30 31 --method 2：insert date into a tempoprary table32 BEGIN TRANSACTION33 GO34 --为了保证临时表中的数据与原表保持一致，我们要在插入数据前给临时表加对应的默认值约束，但是同一个数据库不能建名称一样的约束， --所以这里我们先删除原表的约束，这个操作并不影响现存表中的数据35 ALTER TABLE dbo.RemoveIdentiyTest36 DROP CONSTRAINT DF_RemoveIdentiyTest_col137 GO38 --新建一个临时表39 CREATE TABLE dbo.Tmp_RemoveIdentiyTest40 (41 id int NOT NULL,42 col1 varchar(10) NOT NULL,43 col2 varchar(10) NULL,44 col3 int NULL,45 col4 int NULL,46 col5 char(10) NULL47 ) ON [PRIMARY]48 GO49 --给临时表加锁50 ALTER TABLE dbo.Tmp_RemoveIdentiyTest SET (LOCK_ESCALATION = TABLE)51 GO52 --为了保证临时表中的数据与原表保持一致，我们要在插入数据前给临时表加对应的默认值约束53 ALTER TABLE dbo.Tmp_RemoveIdentiyTest ADD CONSTRAINT54 DF_RemoveIdentiyTest_col1 DEFAULT ('aaa') FOR col155 GO56 --导数据57 IF EXISTS(SELECT * FROM dbo.RemoveIdentiyTest)58 EXEC('INSERT INTO dbo.Tmp_RemoveIdentiyTest (id, col1, col2, col3, col4, col5)59 SELECT id, col1, col2, col3, col4, col5 FROM dbo.RemoveIdentiyTest WITH (HOLDLOCK TABLOCKX)')60 GO61 --删除原表62 DROP TABLE dbo.RemoveIdentiyTest63 GO64 --将临时表更名为正式表65 EXECUTE sp_rename N'dbo.Tmp_RemoveIdentiyTest', N'RemoveIdentiyTest', 'OBJECT' 66 GO67 --添加相应的索引68 ALTER TABLE dbo.RemoveIdentiyTest ADD CONSTRAINT69 PK_RemoveIdentiyTest PRIMARY KEY CLUSTERED 70 (71 id72 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]73 74 GO75 CREATE NONCLUSTERED INDEX IX_RemoveIdentiyTest_col3_col4 ON dbo.RemoveIdentiyTest76 (77 col3,78 col479 ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]80 GO81 CREATE NONCLUSTERED INDEX IX_RemoveIdentiyTest_col5 ON dbo.RemoveIdentiyTest82 (83 col584 ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]85 GO86 COMMIT 方法三：也是通过脚本实现，但是这里我们使用switch to的方式导数据（这是从分区表借鉴过来的），当然这个两个表switch to有一些限制条件，比如两个表有相同的表结构，两个表必须在同一个数据库等。
1 use Test 2 go 3 if OBJECT_ID('dbo.RemoveIdentiyTest') is not null 4 drop table dbo.RemoveIdentiyTest 5 go 6 --新建一个测试表 7 create table dbo.RemoveIdentiyTest 8 ( 9 id int identity not null,10 col1 varchar(10) not null constraint DF_RemoveIdentiyTest_col1 default 'aaa',11 col2 varchar(10) null,12 col3 int null,13 col4 int null,14 col5 char(10) null15 constraint PK_RemoveIdentiyTest primary key clustered16 (17 id asc18 )19 ) ON [PRIMARY]20 go21 create nonclustered index IX_RemoveIdentiyTest_col3_col4 on dbo.RemoveIdentiyTest22 (23 col3,col424 )with(fillfactor=80) on [primary]25 go26 create nonclustered index IX_RemoveIdentiyTest_col5 on dbo.RemoveIdentiyTest27 (28 col529 )with(fillfactor=80)on [primary]30 31 go32 33 --插入测试数据34 declare @i int,@a int,@b int35 set @i=036 set @a=1000037 set @b=038 while(@i<100000)39 begin40 insert into dbo.RemoveIdentiyTest41 select 'aa'+cast(@i as varchar)+'ff','bb'+cast(@i as varchar)+'dd',@a,@b,'A'+cast(@i as varchar)42 set @i=@i+1;43 set @a=@a-1;44 set @b=@b+2;45 end46 go47 48 begin try49 begin transaction 50 51 alter table dbo.RemoveIdentiyTest drop constraint DF_RemoveIdentiyTest_col152 if OBJECT_ID('dbo.tmp_RemoveIdentiyTest') is not null53 drop table dbo.tmp_RemoveIdentiyTest54 create table dbo.tmp_RemoveIdentiyTest55 (56 id int not null,57 col1 varchar(10) not null,58 col2 varchar(10) null,59 col3 int null,60 col4 int null,61 col5 char(10) null62 constraint PK_tmp_RemoveIdentiyTest primary key clustered63 (64 id asc65 )66 ) ON [PRIMARY]67 68 create nonclustered index IX_tmp_RemoveIdentiyTest_col3_col4 on dbo.tmp_RemoveIdentiyTest69 (70 col3,col471 )with(fillfactor=80) on [primary]72 73 create nonclustered index IX_tmp_RemoveIdentiyTest_col5 on dbo.tmp_RemoveIdentiyTest74 (75 col576 )with(fillfactor=80)on [primary]77 --这里使用switch to的方式，比insert 导数据的效率要高很多78 alter table dbo.RemoveIdentiyTest SWITCH to dbo.tmp_RemoveIdentiyTest79 alter table dbo.tmp_RemoveIdentiyTest add constraint DF_RemoveIdentiyTest_col1 DEFAULT ('aaa') FOR col180 drop table dbo.RemoveIdentiyTest81 exec sp_rename N'Test.dbo.tmp_RemoveIdentiyTest.IX_tmp_RemoveIdentiyTest_col3_col4',N'IX_RemoveIdentiyTest_col3_col4',N'index'82 exec sp_rename N'Test.dbo.tmp_RemoveIdentiyTest.IX_tmp_RemoveIdentiyTest_col5',N'IX_RemoveIdentiyTest_col5',N'INDEX'83 exec sp_rename N'PK_tmp_RemoveIdentiyTest',N'PK_RemoveIdentiyTest',N'object'84 exec sp_rename 'Tmp_RemoveIdentiyTest', 'RemoveIdentiyTest','object'85 commit86 end try87 begin catch88 print 'error'89 rollback tran90 end catch
对于以上三种方式，我个人认为如果数据量不是很大，我们可以直接采用第一种或者第二种，但是如果数据量比较大，我们没有足够的磁盘空间或者修改该表所需时间紧迫的情况下，我们可以采用第三种方式。