如何在SQL Server 2008中编写存储过程,以整数数据类型生成两列300万个随机数。
如何在SQL Server 2008中编写存储过程,以整数数据类型生成两列300万个随机数。
SELECT TOP 3000000
ABS(CHECKSUM(NewId())) As RndCol1,
ABS(CHECKSUM(NewId())) AS RndCol2
FROM
sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4
[您可能需要使用卡方检验来检查这些数字的实际分布情况]
更新: 这些数字的随机性可能不符合卡方分布检验。建议根据您的情况进行测试。
create procedure dbo.GiveMeRandomNumbers
as
begin
with qry as (
select CAST(CAST(NEWID() AS VARBINARY) AS INT) as col1
, CAST(CAST(NEWID() AS VARBINARY) AS INT) as col2
, 0 as i
union all
select CAST(CAST(NEWID() AS VARBINARY) AS INT) as col1
, CAST(CAST(NEWID() AS VARBINARY) AS INT) as col2
, i + 1
from qry
where i < 3000000
)
select col1, col2
from qry
option (maxrecursion 0)
end
这里使用 newid
因为对于 CTE 的每个递归应用,rnd
函数将返回相同的结果。