自增列的基本操作

/*自增列的基本操作[為已存在的列按自增值更新]*/--表if object_id('t') is not null drop table tgocreate table t(id int,col nvarchar(20))go--測試數據insert t select null,'test1'union all select null,'test2'union all select null,'test2'union all select null,'test1'go--第一種:按指定順序設置id(但只能更新原有数据，設置不了自增，不算)update tset id=t1.rn    from (select row_number() over(order by col) rn,col from t)t1where t.col=t1.col;goselect * from t order by col;go--第二種：根據數據庫實際存儲數據設置自增idalter table t drop column id;alter table t add id int identity(1,1) not null;goselect * from t;go--第三種：根據自已設置的順序設置自增idif object_id('t_copy') is not null drop table t_copyselect col into t_copy from t order by col;truncate table t;alter table t drop column id;alter table t add id int identity(1,1) not null;insert into t(col) select col from t_copy;goselect * from t order by col;go--第四種：根據自已設置的順序設置自增id，且保持原列順序if object_id('t_copy') is not null drop table t_copyselect col into t_copy from t order by col;drop table t;select id=identity(int,1,1),col into t from t_copy;goselect * from t order by col;go