求SQL获取大数自增ID最快方法

Question

表tb1的主键为id,是VARCHAR格式，形式为20110101000001，即年月日+6位流水号，该主键非自增，但插入新纪录的时候，我要得到当天最大的ID，并加1，如当天（如2011年11月08日）还没有数据，则从20111108000001开始。我写了一个获取函数，每次都SELECT MAX 然后切割后6位再加1再拼字符串，可以得到，但效率不高，如插入1000条数据，每次都要SELECT MAX tb1一次，tb1数据量又大，非常低效。请问有什么方法可以既高效，又能保证插入数据时ID一定不会重复。

Answer 1

如何实现自编号 
http://blog.csdn.net/roy_88/article/details/1424370

Answer 2

插入数据时,一般很少会顾及到效率的问题,因为,数据收集一般不会蜂拥而至,人们更多地关心的是查询时的效率.
你这样做法的效率并非很差,不过得注意防止并发错误,即有个同时读取ID并也同时生成一个相同的ID.一般解决的办法是加事务.
简单的办法,还是用自增列,你用一个自增列,加一个日期列,数据处理上要比你这样处理好上百倍,为什么不这么去做呢?

Answer 3

因为原来是用GUID，后来用了一段时间后，用户说要改成编号，所以插入时的ID就用函数每次去生成，这里效率之所以有要求，是因为发现这个获取在大批量处理时成为了瓶颈。目前系统运行了好长时间，该表加列可能程序中有改动风险。

Answer 4

參照用一個表來記錄最大號
http://topic.csdn.net/u/20111030/18/8e4c77b0-7a88-4276-ba52-b9e7810a5e3d.html

Answer 5

在学习中遇到这个问题 
数据库里有编号字段 
BH00001 
BH00002 
BH00003 
BH00004 
如何实现自动增长 --下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
    RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
    INSERT tb(col) VALUES(1)
    INSERT tb(col) VALUES(2)
    INSERT tb(col) VALUES(3)
    DELETE tb WHERE col=3
    INSERT tb(col) VALUES(4)
    INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH         col 
---------------- ----------- 
BH000001  1
BH000002  2
BH000003  4
BH000004  14
--*/ create table tb
(id int identity,
name varchar(10),
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'select * from tbdrop table tb/*
id          name       code         
----------- ---------- ------------ 
1           A          BH00001
2           B          BH00002
3           C          BH00003
4           D          BH00004（所影响的行数为 4 行）
*/

Answer 6

楼主何不试试用 INSTEAD OF 触发器,在触发器里直接获取ID再插入,可能要比另外写函数方便些,反正该做的事总是得做.用触发器的好处是可以批量插入.

Answer 7

现在的问题是一旦用SELECT MAX这样的语法，就变得很慢，因为我一次插数据很多，插一个，SELECT MAX肯定会变化，所以每次去SELECT 这个表达时候，批量处理就很慢（尽管单独执行该生成序号函数为0秒）。用另外一个B表记录最大值一定程度上加快了，但必须加触发器或把整个系统中的这个表的INSERT语句中都加入执行更新B表的操作。有没有更好的方法。

Answer 8

--创建例表
create table tb(id char(14),col varchar(10))
insert into tb select '20111107000001','aaa'
insert into tb select '20111108000001','bbb'
go
--以下为触发器
create trigger addid
on tb
instead of insert
as
begin
set nocount oninsert into tb
select (
select convert(varchar(8),getdate(),112)+'00000'+ltrim(isnull(max(right(id,6)),0)+1)
from tb where id like convert(varchar(8),getdate(),112)+'%'),COL from inserted
end
go
--插入测试
insert into tb(col) select 'ccc'
select * from tb
/*
id             col
-------------- ----------
20111107000001 aaa
20111108000001 bbb
20111108000002 ccc(3 行受影响)
*/
--删除今日数据后再插入,测试 1 的生成
delete from tb where id like convert(varchar(8),getdate(),112)+'%'
select * from tb
/*
id             col
-------------- ----------
20111107000001 aaa(1 行受影响)
*/
insert into tb(col) select 'ccc'
select * from tb
/*
id             col
-------------- ----------
20111107000001 aaa
20111108000001 ccc(2 行受影响)
*/
go
drop table tb

Answer 9

如果一次可能会插入多条数据,则:
--创建例表
create table tb(id char(14),col varchar(10))
insert into tb select '20111107000001','aaa'
insert into tb select '20111108000001','bbb'
go
--以下为触发器
create trigger addid
on tb
instead of insert
as
begin
set nocount ondeclare @n int
select @n=isnull(max(right(id,6)),0) from tb where id like convert(varchar(8),getdate(),112)+'%'
insert into tb
select convert(varchar(8),getdate(),112)+'00000'+ltrim(rn+@n),col from
(select row_number()over(order by(select 1))rn,col from inserted)t
end
go
--插入测试
insert into tb(col)
select 'ccc' union all
select 'fff' union all
select 'ggg'
select * from tb
/*
id             col
-------------- ----------
20111107000001 aaa
20111108000001 bbb
20111108000002 ccc
20111108000003 fff
20111108000004 ggg(5 行受影响)
*/
--删除今日数据后再插入,测试 1 的生成
delete from tb where id like convert(varchar(8),getdate(),112)+'%'
select * from tb
/*
id             col
-------------- ----------
20111107000001 aaa(1 行受影响)
*/
insert into tb(col)
select 'ccc' union all
select 'fff' union all
select 'ggg'
select * from tb
/*
id             col
-------------- ----------
20111107000001 aaa
20111108000001 ccc
20111108000002 fff
20111108000003 ggg(4 行受影响)
*/
go
drop table tb