Generating never used before random number in SQL

I am using the function below to generate a random number between 0 and 99999999999.

CREATE VIEW [dbo].[rndView]
AS
SELECT RAND() rndResult
GO



ALTER function [dbo].[RandomPass]()
RETURNS NUMERIC(18,0)
 as
    begin
    DECLARE @RETURN NUMERIC(18,0)
    DECLARE @Upper NUMERIC(18,0);
    DECLARE @Lower NUMERIC(18,0);
    DECLARE @Random float;

    SELECT @Random = rndResult
    FROM rndView

      SET @Lower = 0 
      SET @Upper = 99999999999 
      set @RETURN= (ROUND(((@Upper - @Lower -1) * @Random + @Lower), 0))

 return @RETURN
 end;

However, I need to make sure that the returned number has never been used before in the same app. In .net I would create a while loop and keep looping until the returned value is not found in a table that stores previously used values. Is there a way to achieve the same result directly in SQL, ideally without using loops? If there is no way to do that without loops, I think it would still be more efficient to do it in an SQL function rather than having a loop in .net performing a number of query requests.

Answer

You will need to store the used values in a table, and a recursive query to generate the next value.

The answer depends on the RDBMS you are using.

Below are two examples, in PostgreSQL and MS SQL Server, that would solve your problem.

PostgreSQL

First, create a table that will hold your consumed ids :

CREATE TABLE consumed_ids (
  id BIGINT PRIMARY KEY NOT NULL
);

The PRIMARY KEY is not strictly necessary, but it will

1. generate an index which will speed up the next query;
2. ensure that two equal ids are never generated.

Then, use the following query to obtain a new id :

WITH RECURSIVE T AS (
  SELECT 1 AS n, FLOOR(RANDOM() * 100000000000) AS v
  UNION ALL
  SELECT n + 1, FLOOR(RANDOM() * 100000000000)
  FROM T
  WHERE EXISTS(SELECT * FROM consumed_ids WHERE id = v)
)
INSERT INTO consumed_ids
SELECT v
FROM T
ORDER BY n DESC
LIMIT 1
RETURNING id;

The logic is that as long as the (last) id generated is already consumed, we generate a new id. The column n of the CTE is there only to retrieve the last generated id at the end, but you may also use it to limit the number of generated random numbers (for example, give up if n > 10).

(tested using PostgreSQL 12.4)

MS SQL Server

First, create a table that will hold your consumed ids :

CREATE TABLE consumed_ids (
  id BIGINT PRIMARY KEY NOT NULL
);

Then, use the following query to obtain a new id :

WITH T AS (
  SELECT 1 AS n, FLOOR(RAND() * 100000000000) AS v
  UNION ALL
  SELECT n + 1, FLOOR(RAND() * 100000000000)
  FROM T
  WHERE EXISTS(SELECT * FROM consumed_ids WHERE id = v)
)
INSERT INTO consumed_ids (id)
OUTPUT Inserted.id
SELECT TOP 1 v
FROM T
ORDER BY n DESC;

(tested using MS SQL Server 2019).

Note however that MS SQL Server will give up after 100 tries by default.