之前使用的表的主键为id自增，现在想变成由sequence来控制id值的增加，不能删除现有数据，数据一直保持在数据库中。

之前的schema：

create table biz_job_history (
  id BIGINT identity not null
  , job_id VARCHAR(32)
  , start_time DATETIME2
  , end_time DATETIME2
  , status VARCHAR(32)
  , result NVARCHAR(max)
  , created_date DATE default GETDATE() not null
  , created_at DATETIME2 default SYSDATETIME() not null
  , updated_at DATETIME2 default SYSDATETIME() not null
  , updated_by VARCHAR(64) not null
  , constraint biz_job_history_PKC primary key nonclustered (id)
) ;

目标schemal：

create table biz_job_history (
  id BIGINT not null
  , job_id VARCHAR(32)
  , start_time DATETIME2
  , end_time DATETIME2
  , status VARCHAR(32)
  , result NVARCHAR(max)
  , created_date DATE default GETDATE() not null
  , created_at DATETIME2 default SYSDATETIME() not null
  , updated_at DATETIME2 default SYSDATETIME() not null
  , updated_by VARCHAR(64) not null
  , constraint biz_job_history_PKC primary key nonclustered (id)
) ;

if not exists(select * from sys.sequences where name = N'biz_job_history_seq')
create sequence biz_job_history_seq as BIGINT
    start with 1
    increment by 1
    no maxvalue
go;

 

不重新建表，仅修改数据库所需要的操作：

// 新建一列
alter table biz_job_history add id_copy BIGINT not null default 0;
// 之前id的数据储存到新建的一列
update biz_job_history set id_copy=id;
// 删除表的主键
alter table biz_job_history drop constraint biz_job_history_PKC;
// 删除id列
ALTER  TABLE biz_job_history DROP column id;
// 将新建的列重命名为id
exec sp_rename 'biz_job_history.id_copy','id','column'；
//设置id为表的主键
alter table biz_job_history add constraint biz_job_history_PKC primary key nonclustered (id);

// 获取当前数据id的最大值
select top 1 id from biz_job_history order by created_at desc   --时间倒序排列取第一条

// 使用上一个sql获取的最大值val，定义sequence的开始值
if not exists(select * from sys.sequences where name = N'biz_job_history_seq')
create sequence biz_job_history_seq as BIGINT
    start with val
    increment by 1
    no maxvalue
go;