SQL SERVER – Random Number Generator Script – SQL Query
来源:互联网 发布:自动播放软件 编辑:程序博客网 时间:2024/05/16 14:14
Random Number Generator
There are many methods to generate random number in SQL Server.
Method 1 : Generate Random Numbers (Int) between Rang---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
Method 2 : Generate Random Float NumbersSELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
Method 3 : Random Numbers Quick Scripts---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())
Method 4 : Random Numbers (Float, Int) Tables Based with TimeDECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum
Method 5 : Random number on a per row basis---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
- SQL SERVER – Random Number Generator Script – SQL Query
- SQL Server Query Plan
- SQL Server slow query
- Pseduo Random Number Generator
- Hackerrank Random Number Generator
- SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script
- SQL SERVER – Get Last Running Query Based on SPID
- sql server 2000/2005 script
- sql server 2000/2005 script
- Get to know the Random Query Generator
- 当SQL Query跑得很慢的时候...-- Random IO
- 当SQL Query跑得很慢的时候...-- Random IO
- SQL SERVER 分页(Paging a Query with SQL Server)
- sql script
- sql script
- SQL Script
- sql server 版本号(sql version number)
- SQL Server Query Execution Plan Analysis
- JAVA解析XML,DTD验证问题
- javascript实现关闭浏览器时弹出提示框
- deb和rpm常用指令
- 动态多行显示jsp
- Fms教程7 理解共享对象
- SQL SERVER – Random Number Generator Script – SQL Query
- 关于用opencv读写AVI、MPEG格式文件的若干建议
- 参与全国软考调查问卷,赢精彩大奖
- Embedded Linux System 开机流程
- WinXP + openSUSE 卸载openSUSE
- 获得汉字首字母和拼音
- VIM操作指南“大全”附思维图谱便于记忆
- Boost.Bind的基础使用
- 车型识别算法