How SQL Server quickly generates 1 million random 8-bit numbers that are not duplicated

2. Recently in the forum to see someone asked, how to quickly generate 1 million non-repeating 8-digit number, for this issue, there are a few things to note:
6. 1. How to generate a 8-bit random number, the more random the generated number, the more likely the repetition is, of course, the smaller
10. 2. Control does not repeat
14. 3. Consider performance
18. In response to this problem, I have written the following examples to solve, hoping to provide guidance to those who have such needs
22. */
26. Use tempdb
28. GO
32. --Create a test table
34. CREATE TABLE TB (ID char (8))
38. --Create a unique index for automatic filtering of duplicate values
40. CREATE UNIQUE INDEX ix_tb on TB (ID)
42. With Ignore_dup_key
44. GO
48. --Test the processing time of data insertion, and record the point in time at which processing begins
50. DECLARE @dt datetime
52. SET @dt = GETDATE ()
56. --Inserting random data
58. SET NOCOUNT on
60. DECLARE @row int
62. Set @row = 1000000 --Sets the total number of records
64. While @row >0
66. BEGIN
68. -Displays a message indicating that multiple rows of data are also required
70. RAISERROR (' need%d rows ', ten, 1, @row) with NOWAIT
74. --Inserting random bits encoded data
76. SET ROWCOUNT @row
78. INSERT TB SELECT id = right (100000000 + CONVERT (bigint, ABS (CHECKSUM (NEWID ())), 8)
80. From syscolumns c1,syscolumns C2
82. SET @row = @row-@ @ROWCOUNT
84. END
88. --Displays the time that the data was inserted for use
90. SELECT begindate = @dt, EndDate = GETDATE (), Seconds = DATEDIFF (Second, @dt, GETDATE ())
92. GO
96. --Shows whether the final result record is correct
98. SELECT COUNT (*) from TB
104. SET ROWCOUNT 0
106. SET NOCOUNT OFF
108. GO
112. --Delete test
114. DROP TABLE TB
120. /*
124. Techniques used to solve the problem:
128. 1. Control the resulting data is not duplicated, directly using the IGNORE_DUP_KEY option in a unique index, so that duplicate values in the inserted data are automatically filtered to avoid manual processing of duplicates
130. 2. Use CHECKSUM with the NEWID () function to make the generated data as random as possible, and generally generate random numbers to consider using the RAND () function.
132. But this function is to produce pseudo-random values, test with the following statement, you will find that the resulting data are all the same, this does not apply to bulk generation of multiple random numbers,
134. The NEWID () function produces a GUID that is basically not duplicated, and then translates it into a number by checksum, so that the likelihood of duplication is smaller
136. SELECT TOP ten RAND () from sysobjects
138. 3. In efficiency control, use the Loop + batch generation method instead of the traditional one-by-one generation. In SQL Server, each INSERT statement will have an internal transaction,
140. If you insert, the cost of the transaction is too large, the efficiency will be very low, do not consider generating 1 million data at a time, one because the generated data may be duplicated,
142. Remove the repetition there is no 1 million, two one-time generation of 1 million data, consumption of memory and CPU resources is also very high, the general computer may not withstand
144. */

How SQL Server quickly generates 1 million random 8-bit numbers that are not duplicated