SQL自增主键函数

自动生成编码的主键函数

比如

CRM00001

CRM00002

CRM00003

 

 

create   table   IntKey(KeyChar   char(10)) 
go 
create   function   GetKey() 
returns   char(10) 
as 
begin 
declare   @KeyValue   int 
declare   @KeyReturn   varchar(20) 
set   @KeyValue   =   cast(isnull((select   max(KeyChar)   from   IntKey),0)   as   int)   +   1 
set   @KeyReturn   =   '000000000 '   +   ltrim(str(@KeyValue)) 
return   right(@KeyReturn,10) 
end 

go 

declare   @i   int 
set   @i   =   0 
while   @i   <   100 
begin 
insert   into   IntKey(KeyChar) 
select   dbo.GetKey() 
set   @i   =   @i   +   1 
end 
select   *   from   IntKey 
go 
drop   function   GetKey 
drop   table   IntKey