当我们需要去掉一个列上的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