编号处理 （2）

--(1)插入标识列 create table #tt ( id int identity(1,1), col int ) insert into #tt select 1 union all select 2 union all select 3 select * from #tt delete from #tt where id=2 set identity_insert #tt on insert into #tt(id,col) select 2,2 from #tt where id=3 update #tt set id=3 where id=2 --(2)@@identity,scope_identiuty()之间的区别 /* 对于@@identity而言,它返回的是当前会话中任何作用域内的最后插入的一个标识值 对于scope_identity()而言,它返回的是当前作用域内插入的标识值 */ create table t1 ( id int identity(1,1), col int ) create table t2 ( id int identity(100,1), col int ) create trigger tg_t1 on t1 for insert as insert into t2 select col from inserted go insert into t1 select 2 select @@identity,scope_identity() --(3)RowGuidCol /* 如果应用程序需要生成整个数据库或者世界各地所有网络计算机的所有数据库中均为唯一标识符列 就使用RowGuidCol列 */ create table #tr ( id uniqueidentifier rowguidcol primary key default newid(), col uniqueidentifier ) insert into #tr (col) select newid() union all select newid() select rowguidcol,* from #tr --(4)生成流水号 if object_id('tb') is not null drop table tb drop function dbo.FC_Next create function dbo.FC_Next() returns char(8) as begin return (select 'BH'+right(1000001+isnull(right(max(BH),6),0),6) from tb) end create table tb ( BH char(8) primary key default dbo.FC_Next(), col int ) select * from tb begin tran insert into tb (col) values (1) insert into tb (col) values (2) insert into tb(BH,col) values (dbo.FC_Next(),14) commit tran select * from tb