去掉表的identity属性

在我们的业务中，我们经常碰到去掉表的identity属性的需求。但是sql并没有提供可以直接去掉该属性的sql语句。但是其实我们自己可以通过三种方式来实现。

第一种：通过界面操作

将以上图中的yes改为no ，然后点击保存，这里需要注意一个问题，sql server好像默认是不允许这样改，这里我们就需要修改一个选项

 

需要将以上红色框内的选项去掉，这样就可以保存修改，并且保存修改所使用的脚本。

第二种方式，其实就跟上面类似，只是我们可以按照它的原理通过脚本实现。

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]

--method 2：insert date into a tempoprary table
BEGIN TRANSACTION
GO
--为了保证临时表中的数据与原表保持一致，我们要在插入数据前给临时表加对应的默认值约束，但是同一个数据库不能建名称一样的约束，
   --所以这里我们先删除原表的约束，这个操作并不影响现存表中的数据
ALTER TABLE dbo.RemoveIdentiyTest
    DROP CONSTRAINT DF_RemoveIdentiyTest_col1
GO
--新建一个临时表
CREATE TABLE dbo.Tmp_RemoveIdentiyTest
    (
    id int NOT NULL,
    col1 varchar(10) NOT NULL,
    col2 varchar(10) NULL,
    col3 int NULL,
    col4 int NULL,
    col5 char(10) NULL
    )  ON [PRIMARY]
GO
--给临时表加锁
ALTER TABLE dbo.Tmp_RemoveIdentiyTest SET (LOCK_ESCALATION = TABLE)
GO
--为了保证临时表中的数据与原表保持一致，我们要在插入数据前给临时表加对应的默认值约束
ALTER TABLE dbo.Tmp_RemoveIdentiyTest ADD CONSTRAINT
    DF_RemoveIdentiyTest_col1 DEFAULT ('aaa') FOR col1
GO
--导数据
IF EXISTS(SELECT * FROM dbo.RemoveIdentiyTest)
     EXEC('INSERT INTO dbo.Tmp_RemoveIdentiyTest (id, col1, col2, col3, col4, col5)
        SELECT id, col1, col2, col3, col4, col5 FROM dbo.RemoveIdentiyTest WITH (HOLDLOCK TABLOCKX)')
GO
--删除原表
DROP TABLE dbo.RemoveIdentiyTest
GO
--将临时表更名为正式表
EXECUTE sp_rename N'dbo.Tmp_RemoveIdentiyTest', N'RemoveIdentiyTest', 'OBJECT' 
GO
--添加相应的索引
ALTER TABLE dbo.RemoveIdentiyTest ADD CONSTRAINT
    PK_RemoveIdentiyTest PRIMARY KEY CLUSTERED 
    (
    id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_RemoveIdentiyTest_col3_col4 ON dbo.RemoveIdentiyTest
    (
    col3,
    col4
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_RemoveIdentiyTest_col5 ON dbo.RemoveIdentiyTest
    (
    col5
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

方法三：也是通过脚本实现，但是这里我们使用switch to的方式导数据（这是从分区表借鉴过来的），当然这个两个表switch to有一些限制条件，比如两个表有相同的表结构，两个表必须在同一个数据库等。

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

begin try
    begin transaction 

    alter table dbo.RemoveIdentiyTest drop constraint DF_RemoveIdentiyTest_col1
    if OBJECT_ID('dbo.tmp_RemoveIdentiyTest') is not null
         drop table dbo.tmp_RemoveIdentiyTest
    create table dbo.tmp_RemoveIdentiyTest
    (
     id int  not null,
     col1 varchar(10) not null,
     col2 varchar(10) null,
     col3 int null,
     col4 int null,
     col5 char(10) null
     constraint PK_tmp_RemoveIdentiyTest primary key clustered
     (
       id asc
     )
    ) ON [PRIMARY]

    create nonclustered index IX_tmp_RemoveIdentiyTest_col3_col4 on dbo.tmp_RemoveIdentiyTest
    (
     col3,col4
    )with(fillfactor=80) on [primary]

    create  nonclustered index IX_tmp_RemoveIdentiyTest_col5 on dbo.tmp_RemoveIdentiyTest
    (
     col5
    )with(fillfactor=80)on [primary]
     --这里使用switch to的方式，比insert 导数据的效率要高很多
    alter table dbo.RemoveIdentiyTest SWITCH  to dbo.tmp_RemoveIdentiyTest
    alter table dbo.tmp_RemoveIdentiyTest add constraint DF_RemoveIdentiyTest_col1  DEFAULT ('aaa') FOR col1
    drop table dbo.RemoveIdentiyTest
    exec sp_rename  N'Test.dbo.tmp_RemoveIdentiyTest.IX_tmp_RemoveIdentiyTest_col3_col4',N'IX_RemoveIdentiyTest_col3_col4',N'index'
    exec sp_rename  N'Test.dbo.tmp_RemoveIdentiyTest.IX_tmp_RemoveIdentiyTest_col5',N'IX_RemoveIdentiyTest_col5',N'INDEX'
    exec sp_rename  N'PK_tmp_RemoveIdentiyTest',N'PK_RemoveIdentiyTest',N'object'
    exec sp_rename 'Tmp_RemoveIdentiyTest', 'RemoveIdentiyTest','object'
   commit
end    try
begin catch
    print 'error'
    rollback tran
end catch

 

对于以上三种方式，我个人认为如果数据量不是很大，我们可以直接采用第一种或者第二种，但是如果数据量比较大，我们没有足够的磁盘空间或者修改该表所需时间紧迫的情况下，我们可以采用第三种方式。