create table test(id char(5) primary key ,col varchar(4)) go create function uf_maxid() returns char(5) as begin declare @maxid varchar(5) select @maxid = max(id) from test if @maxid is null set @maxid = '00000' return right('00000'+ltrim(cast(@maxid as int)+1),5) end goalter table test add CONSTRAINT column_e_default DEFAULT dbo.uf_maxid() for id goinsert into test(col) select 'test' insert into test(col) select 'test' insert into test(col) select 'test'select * from test /* id col ----- ---- 00001 test 00002 test 00003 test(所影响的行数为 3 行) */drop table test drop function uf_maxid
go
create function uf_maxid()
returns char(5)
as
begin
declare @maxid varchar(5)
select @maxid = max(id) from test
if @maxid is null set @maxid = '00000'
return right('00000'+ltrim(cast(@maxid as int)+1),5)
end
goalter table test add CONSTRAINT column_e_default
DEFAULT dbo.uf_maxid() for id
goinsert into test(col) select 'test'
insert into test(col) select 'test'
insert into test(col) select 'test'select * from test
/*
id col
----- ----
00001 test
00002 test
00003 test(所影响的行数为 3 行)
*/drop table test
drop function uf_maxid