SQL Server random number function test

Random numbers should be used in the stored procedure by accident to have doubts about their probability. Therefore, we should first test the SQL random number function.

 

The SQL Server's random number function is rand (), which generates a floating point number smaller than 1, for example, 0.123456789. If you need to limit the size, multiply it by a number and then get the integer.

 

For example, 0 ~ Random Number in the 99 range: Select cast (RAND () * 100 as INT)

For example, 0 ~ Random Number in the range of 999: Select cast (RAND () * 1000 as INT)

And so on

 

However, you have to write a small program to test the average probability of random numbers.

 

-- To create a temporary table, you only need an integer field valdrop table # testrandgocreate table # testrand (Val int null) goselect * from # testranddeclare @ I intset @ I = 1 -- 0.25 million cycles, that is, insert 2.5 million records and then insert a random number between 0 and 10 into the database insert # testrand ([Val]). values (select cast (RAND () * 10 as INT) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT ))) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT ))) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT ))) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT) insert # testrand ([Val]) values (select cast (RAND () * 10 as INT) set @ I = @ I + 1end -- list the number of records inserted from 0 to 9 respectively, calculate the average select (select count (*) from # testrand where [Val] = 0) as [0], (select count (*) from # testrand where [Val] = 1) as [1], (select count (*) from # testrand where [Val] = 2) as [2], (select count (*) from # testrand where [Val] = 3) as [3], (select count (*) from # testrand where [Val] = 4) as [4], (select count (*) from # testrand where [Val] = 5) as [5], (select count (*) from # testrand where [Val] = 6) as [6], (select count (*) from # testrand where [Val] = 7) as [7], (select count (*) from # testrand where [Val] = 8) as [8], (select count (*) from # testrand where [Val] = 9) as [9], (select cast (sum (VAL) as float)/2500000 from # testrand) as [average]

First running result (0 ~ 9)

0	1	2	3	4	5	6	7	8	9	Average
249715	250738	249181	251119	250237	249910	249324	249997	249787	249992	4.4988408

 

Second running result

0	1	2	3	4	5	6	7	8	9	Average
250676	250294	249953	250377	250108	249191	250322	250373	249817	248889	4.4963192

 

Third running result

0	1	2	3	4	5	6	7	8	9	Average
249999	250287	250635	249590	249637	249360	249768	250637	249947	250140	4.4998312

 

Maximum Value: 251119, minimum value 248889, total amount deviation (251119-248889)/250000 = 0.00892, less than 1%, acceptable, average is also close to 4.5, also OK, the above is the test result of the SQL random number function for your reference.

 

Article Source

Q discussion: 236201801

.