sql 生成100W条指定位数的随机数的方法(只花费了不足1分钟)(整理)

 

应用例子:比如饮料的中奖号码,N多瓶每瓶有个随机号码

o觉得有些地方值得学习:

1.一次性插入尽可能多的记录(不判断是否已经存在该随机数),而不是一次插入一条记录(插入前要判断是否已经存在该随机数).这里重复记录通过设置忽略重复记录的索引来实现的.
 "一次性的插入尽可能多的记录"和"忽略重复记录的索引"节约的时间简直太多了....我使用建立索引后的循环插入50W记录都花了2-3个小时....

2.SET NOCOUNT ON 这样也可以节约时间 ,但测试发现设置为OFF或ON,其时间几乎相同的都是47或46秒左右

3.这里虽然也使用了循环,但它的循环次数是不固定的.其次数取和每次插入失败的记录数有关.失败的记录数为0的时候,任务也就完成了.我测试需要循环50次左右就可以了.

4.syscolumns和sysobjects两个表的作用是获得足够数量的随机数,两个表的交叉查询记录大于100W就可以了.

--生成8位长度的100W条记录的随机数,插入数据库.
USE tempdb
GO
CREATE TABLE tb(id char(8))
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 = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
 --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
DROP TABLE tb

--测试结果:
BeginDate                 EndDate                               Second
2006-10-26 16:03:17.483  2006-10-26 16:04:04.093  47

need 1000000 rows
Duplicate key was ignored.
need 975756 rows
Duplicate key was ignored.
need 951522 rows
Duplicate key was ignored.
need 927292 rows
Duplicate key was ignored.
need 903082 rows
Duplicate key was ignored.
need 878862 rows
Duplicate key was ignored.
need 854648 rows
Duplicate key was ignored.
need 830435 rows
Duplicate key was ignored.
need 806230 rows
Duplicate key was ignored.
need 782030 rows
Duplicate key was ignored.
need 757841 rows
Duplicate key was ignored.
need 733669 rows
Duplicate key was ignored.
need 709495 rows
Duplicate key was ignored.
need 685307 rows
Duplicate key was ignored.
need 661143 rows
Duplicate key was ignored.
need 636988 rows
Duplicate key was ignored.
need 612827 rows
Duplicate key was ignored.
need 588674 rows
Duplicate key was ignored.
need 564527 rows
Duplicate key was ignored.
need 540378 rows
Duplicate key was ignored.
need 516240 rows
Duplicate key was ignored.
need 492132 rows
Duplicate key was ignored.
need 468030 rows
Duplicate key was ignored.
need 443902 rows
Duplicate key was ignored.
need 419780 rows
Duplicate key was ignored.
need 395707 rows
Duplicate key was ignored.
need 371617 rows
Duplicate key was ignored.
need 347528 rows
Duplicate key was ignored.
need 323431 rows
Duplicate key was ignored.
need 299338 rows
Duplicate key was ignored.
need 275260 rows
Duplicate key was ignored.
need 251171 rows
Duplicate key was ignored.
need 227119 rows
Duplicate key was ignored.
need 203050 rows
Duplicate key was ignored.
need 178982 rows
Duplicate key was ignored.
need 154966 rows
Duplicate key was ignored.
need 130920 rows
Duplicate key was ignored.
need 106881 rows
Duplicate key was ignored.
need 82863 rows
Duplicate key was ignored.
need 58852 rows
Duplicate key was ignored.
need 34807 rows
Duplicate key was ignored.
need 10777 rows
Duplicate key was ignored.
need 104 rows
Duplicate key was ignored.
need 1 rows