SQL百万级别随机数批量生成

最近在论坛看到有人问，如何快速生成100万不重复的8位编号，对于这个问题，有几点是需要注意的：

1．    如何生成8位随机数，生成的数越随机，重复的可能性当然越小

2．    控制不重复

3．    考虑性能

针对这个问题，我写了如下的示例来解决，希望能为有这类需求的人提供指导

先创建视图: 

create   view   v_rand   
  as   
          select   re=stuff(rtrim(cast(rand()   as   numeric(7,6))),1,2,'')   
  go 

再创建函数:

--生成随机编号的函数    
CREATE FUNCTION f_RANDBH(@BHLen int)   
RETURNS varchar(50)   
AS   
BEGIN   
    DECLARE @r varchar(50)   
    IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)   
        SET @BHLen=10   
   
    SELECT @r=CHAR(65   
            +(SUBSTRING(re,1,1)   
            +SUBSTRING(re,2,1)   
            +SUBSTRING(re,3,1))%26)   
        +CHAR(65   
            +(SUBSTRING(re,4,1)   
            +SUBSTRING(re,5,1)   
            +SUBSTRING(re,6,1))%26)   
    FROM v_RAND   
    WHILE LEN(@r)<@BHLen   
        SELECT @r=@r+CHAR(65   
                +(SUBSTRING(re,1,1)   
                +SUBSTRING(re,2,1)   
                +SUBSTRING(re,3,1))%26)   
            +CHAR(65   
                +(SUBSTRING(re,4,1)   
                +SUBSTRING(re,5,1)   
                +SUBSTRING(re,6,1))%26)   
        FROM v_RAND   
    RETURN(LEFT(@r,@BHLen))   
END   

下面是执行SQL生成百万级随机数：

USE tempdb  用数据库
GO
CREATE TABLE tb(id varchar(12))
CREATE UNIQUE INDEX IX_tb ON tb(id)
WITH IGNORE_DUP_KEY  -- --忽略重复记录的索引
GO
DECLARE @dt datetime
SET @dt = GETDATE()
SET NOCOUNT OFF
DECLARE @row int
SET @row = 1000000 --记录总数为100W
WHILE @row >0
BEGIN
 RAISERROR('need %d rows', 10, 1, @row) WITH NOWAIT
 SET ROWCOUNT @row
 INSERT tb SELECT
 id = dbo.f_RANDBH(4)+RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 6)+dbo.f_RANDBH(2)
 --FROM syscolumns c1, syscolumns c2  -- SET NOCOUNT ON 49 Second
 FROM syscolumns c1, sysobjects o -- SET NOCOUNT ON 47 Second
 SET @row = @row - @@ROWCOUNT  --设置下次循环需要插入的记录数量
END
SELECT BeginDate = @dt, EndDate = GETDATE(), Second = DATEDIFF(Second, @dt, GETDATE())
GO
SELECT COUNT(*) FROM tb
GO

原文地址:http://blog.sina.com.cn/s/blog_660d152c0100z4l9.html