--newid()保证每次都随机
--checksum 转成数字,可能有负数
--rand保证数字都是正数,且数字足够大
--转成bigint保证不会出现e+,科学计数法
--随机整数 SELECT CHECKSUM(NEWID()) 返回随机数1到n: abs(CHECKSUM(NEWID()))%n + 1
--随机浮点数 rand(CHECKSUM(NEWID()))
--随机固定数位随机数:SELECT LEFT(CONVERT(VARCHAR(20),CONVERT(BIGINT,RAND(CHECKSUM(NEWID())) * 1e+16)) + '0000',16)
SELECT TOP 10 LEFT(r + '0000',16)
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
CROSS APPLY( SELECT CONVERT(VARCHAR(20),CONVERT(BIGINT,RAND(CHECKSUM(NEWID())) * 1e+16)) r ) m
SELECT TOP 2000 rCode --排重
FROM
(
SELECT DISTINCT rCode
FROM (
SELECT LEFT(r,6) rCode
FROM sys.all_columns a
CROSS APPLY( SELECT ABS(CHECKSUM(NEWID())) r ) m
)M
--WHERE NOT EXISTS( SELECT 1 FROM CouponBasicInfo cbi WHERE cbi.CouponNo = m.CouponNo )
) N
注意:abs(checksum(newid()))会出现重复,见下例:
--正负
SELECT CHECKSUM('22699D09-DBF8-4147-996A-16FF92A5EBFB')
SELECT CHECKSUM('97293ABF-B3A8-4427-BE65-21F3A92EAE2A')
--双负
SELECT CHECKSUM('9F5BE5EA-D4C7-4207-AAB0-046B35795557')
SELECT CHECKSUM('16A51019-E786-4FDD-B661-8C9353B20053')
--双正
SELECT CHECKSUM('E60F036B-86FB-48D0-AD09-DB0B7E6091EC')
SELECT CHECKSUM('9106BB7D-7228-4649-8511-0C96780F1917')
参考:http://www.sqlservercentral.com/articles/Data+Generation/87901/