The Random Number of Oracle sqlserver .)

 

In database operations, I often encounter the problem of using random numbers. A few days ago, Xiang Ge asked me about SQL Server's batch generation of random numbers. I remember that he also asked me questions about random numbers under oracle, at that time, I found some materials, but I had no time to sort them out. Today I just sorted out the random numbers in Oracle and sqlserver.

1. Generate a random number

Oracle
The random number in Oracle can be implemented using the dbms_random package. He calls the random number generator in Oracle to generate a random number. For details, refer to the document. For more details, see psoug.
Sqlserver
The rand function can be used to generate random numbers in sqlserver. However, this function seems to generate only one random number at a time. If a random number is generated in large batches, the random numbers generated by dbms_random in Oracle are repeated):
After selecting top 5 rand () from random, Google found an article created by the random, cleverly using checksum (newid () to generate a random number, use the right function to obtain the number of digits (I think it is better to use left to prevent the first digit from being 0, so as to avoid the decrease in the number of digits when data is inserted). This not only achieves the generation of random numbers in batches, efficiency is also good.
2. Random fetch from table

Oracle
This problem was previously encountered in Oracle to study random numbers. It was very interesting to find that the methods can be summarized into two categories. One is to sort data randomly by random numbers. This type of method can be divided into two methods, one is to use dbms_random to generate a random number:
Select * from (select * from t order by dbms_random.value) Where rownum <50;
According to jametong, since random number generator initialization takes time and the database is frequently read during random number generation, this method is the most efficient. The second method is to use dbms_utility.get_hash_value to generate a random number:
Select * from (select transform (to_char (dbms_utility.get_time) | tb1.col1,) rand_num, A. * From tb1 order by rand_num) Where rand_num <= 50; this method is more efficient. The second method is to sample the table with sample, which is the most efficient method:
Select EMP from EMP sample (10); sample (n) is a very useful method, which randomly extracts n % of data from the table, but not necessarily n % may be less than this value, so if a total of 1000 rows of data are to be randomly set to 100 rows, it is best to set N to greater than 10. In addition, this method can only be used for one table, and there cannot be multiple tables after from.

Sqlserver
SQL Server only needs the order by method. Because rand () is duplicated, you can still use the newid () method: Select top 10 * From tablename order by newid ()

....