Random Weight to 1 million non-repeated random Encoding

Zjcxc

Recently, someone asked me in the forum how to quickly generate an 8-bit number that is not repeated in 1 million. There are several points to note for this question:
1. How to generate an 8-bit random number. The more random the number is, the less likely it is to be repeated.
2. Control is not repeated
3. Consider Performance
To solve this problem, I wrote the following example, hoping to provide guidance for those who have such requirements.

Example of generating 1 million pieces of 8-bit non-duplicated data

Use tempdb
Go

-- Create a test table

Create Table Tb (ID char (8 ))

-- Create a unique index used to automatically filter duplicate values

Create unique index ix_tb on TB (ID)
With ignore_dup_key
Go

-- Test the processing time of data insertion and record the start time of processing

Declare @ DT datetime
Set @ dt = getdate ()
-- Insert random data

Set nocount on

Declare @ row int
Set @ ROW = 1000000 -- set the total number of records

While @ row> 0
Begin
-- Displays a message indicating that multiple rows of data need to be inserted.
Raiserror ('need % d rows ', 10, 1, @ row) with Nowait
-- Insert random bit encoding data

Set rowcount @ row

Insert TB select
Id = right (100000000 + convert (bigint, ABS (checksum (newid (), 8)
From syscolumns C1, syscolumns C2

Set @ ROW = @ row-@ rowcount
End

-- Display the time when data is inserted
Select begindate = @ DT, enddate = getdate (),
Second = datediff (second, @ DT, getdate ()),
Go
-- Display whether the final result record is correct
Select count (*) from TB
Go
-- Delete test
Drop table TB

Tips used in solution:

1. control that the generated data is not duplicated. directly use the ignore_dup_key option in the unique index to automatically filter duplicate values in the inserted data to avoid manual duplication.

2. use checksum with the newid () function to make the generated data as random as possible. Generally, the Rand () function is used to generate a random number, but this function generates a pseudo random value, test with the following statement and you will find that all the generated data is the same. This is not suitable for generating multiple random numbers in batches, while the newid () function generates guid, basically, there will be no duplicates, and then convert them into numbers through checksum, which makes it less likely to generate duplicates.