The hard process of using the Rand function of the T-SQL to generate a random number

Source: Internet
Author: User

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> in the previous article minor-latin; mso-fareast-font-family:; mso-fareast-theme-font: minor-fareast;
Mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the speed of inserting large amounts of data is improved; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family:
; Mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri;
In mso-hansi-theme-font: minor-latin ">, a column of data uses Rankmso-fareast-font-family:; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family:
Calibri; mso-hansi-theme-font: minor-latin "> functions are produced to counterfeit data.

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the problem is, what if I want to update the value of this column? Maybe you can delete all of them. Just insert them again. But it's too slow. So I wrote the following code.

 

Color: blue; mso-font-kerning: 0pt; mso-no-proof: yes '> Update0pt; mso-no-proof: yes '> aTable Set aColumn = @ MaxInt * RAND ()

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the results are totally unexpected, the values in this column are all the same!

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> okay, you can give it to Rand () mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> a seed. Just put IDmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the column value is passed to Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> function.

 

Color: blue; mso-font-kerning: 0pt; mso-no-proof: yes '> Update0pt; mso-no-proof: yes '> aTable Set aColumn = @ MaxInt * RAND (ID)

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> result: IDmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> similar Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> values are also very close, as shown below.

 

1 1532427136

2 1532467150

3 1532507164

4 1532547178

5 1532587192

6 1532627206

7 1532667220

8 1532707234

9 1532747248

10 1532787262

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> NO, RANDmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the function can be so linear. ~~~~~~~~ Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> basically, IDmso-ascii-theme-font can be calculated: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin ">=11mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin ">, Rankmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> function value. Fortunately, the results of each run are different.

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> give Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the function transmits a non-linear value. The first thought is to use an exclusive or operation. What is the exclusive or? The first thought is time.

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the following code is available.

 

Color: blue; mso-font-kerning: 0pt; mso-no-proof: yes '> Update0pt; mso-no-proof: yes'> aTable Set aColumn = Convert (int, RAND (datediff (MS, '2017-08-28 23:13:00 ', GETDATE () ^ ID) * @ MaxInt)

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> below is the running result.

 

1 1194104621

2 1194224663

3 1194184649

4 1194304691

5 1194264677

6 1194384719

7 1194344705

8 1194464748

9 1194424733

10 1194544776

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> fortunately, it's not linear, however, this value is still wandering in a small range.

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> I wanted to put Seedmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> a bit messy, if Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> function, make a messy Seedmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> and Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> what are functions? Isn't the output that converts a linear input into a non-linear output Seedmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> does the function work?

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> however, the last step is to save the country. Basic Assumptions: aColumnCalibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> the column is sufficient for Randommso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin ">. So update this column.

 

Color: blue; mso-font-kerning: 0pt; mso-no-proof: yes '> Update0pt; mso-no-proof: yes '> aTable Set aColumn = @ MaxInt * RAND (aColumn)

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> you can also choose Insertmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> aColumnCalibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin ">.

 

Mso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> is there any way to skip Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> transmits a random Seedmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> This allows Randmso-ascii-theme-font: minor-latin; mso-fareast-font-family:; mso-fareast-theme-font:
Minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin "> must a random number be returned?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.