Create a list of new unique random numbers in T-SQL

Question

I need a stored procedure to create records @n, each of which has a unique random 8-digit number. This number should not be incremental and should not already exist in the table.

CREATE TABLE Codes
(
    ID UNIQUEIDENTIFIER PRIMARY KEY,
    Code INT,
    CONSTRAINT UQ_Code UNIQUE(Code) 
);

I can generate random numbers:

DECLARE @min int = 0,
        @max int = 99999999,
        @n INT = 100;

SELECT TOP (@n) FLOOR(CAST(CRYPT_GEN_RANDOM(4) AS BIGINT) / 4294967296 * ((@max - @min) + 1)) + @min
FROM   sys.all_objects s1 
              CROSS JOIN sys.all_objects s2;

But what I'm trying to figure out is how to atomize and paste numbers @ninto a table [Codes], while at the same time providing the opportunity to avoid collisions. Can this be done without a loop?

"must not be incremental" , SP , "1, 2, 3, 4" . , , , .

Answer 1

cte distinct , :

;with cte_stream as (
    select
        floor(cast(crypt_gen_random(4) as bigint) / 4294967296 * ((@max - @min) + 1)) + @min as Code
    from sys.all_objects as s1 
        cross join sys.all_objects as s2;
)
insert into [Codes]
select distinct top (@n) s.Code
from cte_stream as s
where not exists (select * from [Codes] as c where c.Code = s.Code)

, distinct exists, [Codes], order by newid()

Answer 2

:

    DECLARE @min int = 0,
        @max int = 99999999,
        @n INT = 100;

Insert Into Codes(ID, Code)
SELECT G, RandomNumber
From (
    Select TOP (@n) NEWID() As G, FLOOR(CAST(CRYPT_GEN_RANDOM(4) AS BIGINT) / 4294967296 * ((@max - @min) + 1)) + @min As RandomNumber
    FROM   sys.all_objects s1 
                  CROSS JOIN sys.all_objects s2) AS Ran
Where RandomNumber Not IN (Select Code From Codes);

: where, .

Answer 3

:

DECLARE @c INT = 10

;WITH cteDigits AS (SELECT d FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(d)),
cteCombinations AS (SELECT @c + 100 AS rn
UNION ALL
SELECT rn + 1 FROM cteCombinations WHERE rn < @c + 99 + @c
)
SELECT 99999999 - ca1.d*10000000 
                - ca2.d*1000000 
                - ca3.d*100000 
                - ca4.d*10000 
                - ca5.d*1000
                - ca6.d*100 
                - ca7.d*10 
                - ca8.d AS Code
FROM cteCombinations c
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d < 9 AND d.d <> c.rn ORDER BY NEWID()) ca1
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca2
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca3
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca4
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca5
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca6
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca7
CROSS APPLY(SELECT TOP 1 d FROM cteDigits d WHERE d.d <> c.rn ORDER BY NEWID()) ca8
OPTION(MAXRECURSION 0)

:

Code
82520154
41164702
16701568
23744767
34570681
18158118
17548441
57261417
18272038
16576412

, 1 to 9

1, 4, 6, 2, 8, 9, 4, 3
5, 8, 1, 1, 5, 7, 5, 1
....

99999999.

EDIT:

, :

Insert Into Codes
Select Top(@n) t.Code
From(
SELECT FLOOR(CAST(CRYPT_GEN_RANDOM(4) AS BIGINT) / 4294967296 * ((@max - @min) + 1)) + @min AS Code
FROM   sys.all_objects s1 
              CROSS JOIN sys.all_objects s2) t
Left Join Codes c on t.Code = c.Code
Where c.Code Is NULL

EDIT2:

, :

CREATE TABLE Codes(ID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY, Code BIGINT NOT NULL, IsUsed BIT NOT NULL)
GO

;WITH t AS(
SELECT 10000000 + ROW_NUMBER() OVER(ORDER BY (SELECT 1)) rn 
FROM 
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t1(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t2(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t3(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t4(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t5(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t6(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t7(n)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS t8(n)
)

INSERT INTO Codes
SELECT rn, 0 FROM t WHERE rn <= 99999999
ORDER BY NEWID()

CREATE CLUSTERED INDEX someindex ON codes(code)
GO

CREATE INDEX someindex2 ON codes(IsUsed)
GO

10 . update :

;WITH    cte
          AS ( SELECT TOP 1000
                        *
               FROM     dbo.Codes
               WHERE    IsUsed = 0
               ORDER BY id
             )
    UPDATE  cte
    SET     IsUsed = 1
    OUTPUT  Inserted.Code

It updates the bit and returns updated codes. It uses index search and therefore is very fast and updates and returns 100,000 rows in 1 second.