去掉identity列属性范例

当我们需要去掉一个列上的identity属性时。

我们需要做的就是：创建一个完全一样的新表，再将数据转移仅新表中，删除旧表。重命名新表。

下面就是一个此过程的范例，值得注意的是，当旧表数据量很大时，为了避免大批量的数据转移我们采用了关键字swich to这大大节约了性能消耗成本

	alter table dbo.RemoveIdentiyTest SWITCH  to dbo.tmp_RemoveIdentiyTest

下面为范例脚本全文

--更改输出->option->Designers->prevent saving changes that require table re-creation
use Test
go
if OBJECT_ID('dbo.RemoveIdentiyTest') is not null
   drop table dbo.RemoveIdentiyTest
go
create table dbo.RemoveIdentiyTest
(
 id int identity not null,
 col1 varchar(10) not null constraint DF_RemoveIdentiyTest_col1 default 'aaa',
 col2 varchar(10) null,
 col3 int null,
 col4 int null,
 col5 char(10) null
 constraint PK_RemoveIdentiyTest primary key clustered
 (
   id asc
 )
) ON [PRIMARY]
go
create nonclustered index IX_RemoveIdentiyTest_col3_col4 on dbo.RemoveIdentiyTest
(
 col3,col4
)with(fillfactor=80) on [primary]
go
create  nonclustered index IX_RemoveIdentiyTest_col5 on dbo.RemoveIdentiyTest
(
 col5
)with(fillfactor=80)on [primary]

go
declare @i  int,@a int,@b int
set @i=0
set @a=10000
set @b=0
while(@i<100000)
	begin
		insert into dbo.RemoveIdentiyTest
		select 'aa'+cast(@i as varchar)+'ff','bb'+cast(@i as varchar)+'dd',@a,@b,'A'+cast(@i as varchar)
		set @i=@i+1;
		set @a=@a-1;
		set @b=@b+2;
	end
go

set statistics time on
set statistics io  on

--method 1:alter table switch to 
begin try
    begin transaction 
    alter table dbo.RemoveIdentiyTest drop constraint DF_RemoveIdentiyTes