首页 > 代码库 > 去掉表的identity属性

去掉表的identity属性

2024-07-10 10:15:23   215人阅读

在我们的业务中，我们经常碰到去掉表的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

 

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

 


联系
我们