Random Number Generation using SQL

Introduction

It might be required to generate random numbers for one of the following or other reasons.

• To provide unique IDs for records in a database
• To provide unique file names for a file in IO system
• To provide unique random IDs for customers, orders, communications, objects or resources in a system
• To provide unique tracking IDs for goods and services

Background

What is linear congruential method?

It is a sequence of numeric values X₁, X_2,,X₃ ,_… between zero and n-1 by using the following recursive relationship:

X_i+1= (aX_i + c) mod m, where i=0, 1, 2,...

Output: X₀, X₁, X₂,… where X₀ is called seed.

Note

1. The total number of possible random numbers generated depends on the value of a, c, m and X₀.
2. If c and m are relatively prime, then random numbers are in the interval between [0, m-1].
3. The value of a is equals to 1 + 4*j ; where j=1, 2, 3,…

Using the Code

Having the above principle, let us consider how to adopt the linear congruential method to generate random numbers.

Let us assume that XYZ Company has PCI requirement to assign for each of its customers unique random number with digits of 12. Provide a possible solution for XYZ Company to be able to generate the required possible random numbers with 12 digits.

Proposed Solution by XYZ Company IT Department

• 7 digits of cycle length for a random number
• 2 digits of current year
• 2 digits of current date month
• 1 digit of shift (if morning 1, else 2)

How to Implement the Above Proposed Solution Using Linear Congruential Method?

Step 1

Find 7 digits cycle length for the random number.

Using linear congruential, we might have a sequence of numbers X₀, X₁, X_2, … with the following relationship:

X_i+1 = [(aX_i + c) mod m] --------------------------- (1)

Choice a= 17 and c= 13 and m = 2²³ = 8388608 and X₀ = 13 with GCF(c=13, m=2²³) = 1, with c and m are relatively prime.

Hence, we can make random numbers with a cycle length of 8388608 with sequence of random numbers with the following relationship:

 X_i+1 = (17X_i + 13) mod 2²³ with X₀ = 13 --------- (2)

Step 2

Cycle length adjustment (period of random numbers), i.e., in this case, IT department decides to be per shift.

Hence, we can recycle the above random number generation using the following relationship:

Y_i+1 = X_i+1 + (Z * 10⁷) where X_i+1 from equation (2) and Z is with format smmyy where yy is the current year, mm is the current month and s is the current shift (morning = 1, others = 2).

Output

12 digits random numbers.

SQL Implementation of the above analysis.

Create customer table with fields: customerid, customerTrackingNo, firstName, lastName, sex, address, and isActive.

CREATE TABLE [dbo].[Customer](
       [customerid] [int] IDENTITY(1,1) NOT NULL,
       [customerTrackingNo] [varchar](12) NOT NULL,
       [firstName] [varchar](50) NULL,
       [lastName] [varchar](50) NULL,
       [sex] [varchar](10) NULL,
       [address] [varchar](200) NULL,
       [isActive] [bit] NULL,
);
GO

Create a function “udfGetNextRandomNumber” to calculate @X_i+1 and adjust based on the required number of digits using the following script:

CREATE  FUNCTION [dbo].udfGetNextRandomNumber( @Xi int )
RETURNS varchar(12)
AS
BEGIN
        DECLARE @currentTrackingNo varchar(12);
    DECLARE @lastCustomerid int;
 
    --Linear Congurential variables
    DECLARE @a int, @c int, @m int, @XiNext int;
    DECLARE @year varchar(2), @month varchar(2), @shift varchar(12);
    DECLARE @count int;
 
    --*************************Linear congruential generator *************************** 
  
    SET @a= 17; 
    SET @c = 13;
    SET @m = power(2,23);
 
    SET @XiNext = ((( @a * @Xi ) + @c) % @m);
--***********Adjustment cycle length for random number *********************************
 
    SET @year = (SELECT RIGHT(YEAR(getdate()),2));
    SET @month = (SELECT RIGHT('00' + CAST(DATEPART(mm,getdate()) AS varchar(2)), 2));
    SET @shift = (SELECT LTRIM(Right(REPLACE(CONVERT(nvarchar(MAX),_
    CAST(GetDate() as smalldatetime), 109),':00:000', ' '), 8)));
    SET @shift = Right(Cast(@shift as varchar(12)),2);
 
   if (upper(@shift)= 'AM')
           BEGIN
                SET @shift = '1';
           END
   else
           BEGIN
                 SET @shift = '2';
           END
           SET @shift =   @shift + @month + @year;
         DECLARE @cycleAdjustment bigint;
         DECLARE @nextcycleAdjustment varchar(12);
         SET @cycleAdjustment = CAST(@shift as bigint);
         SET @nextcycleAdjustment = _
         CAST( (@cycleAdjustment  * power(10,7) + @XiNext) AS VARCHAR(12))
     RETURN @nextcycleAdjustment
END
GO

Create a stored procedure called “SPU_AddCustomer” to insert a record into the customer table:

CREATE  PROCEDURE [dbo].[SPU_AddCustomer]
       @firstName varchar(50),
       @lastName varchar(50),
       @sex varchar(10),
       @address varchar(200),
       @isActive bit,
       @SeedX0  int =13
AS
BEGIN
      
DECLARE @customerTrackingNo varchar(12), _
@previousTrackingNo varchar(12),  @count int, @lastCustomerid int ,@Xi int;
      
       --Get Xi for table customer for customerTrackingNo field
       SET @count = (SELECT Count(*) FROM Customer)
       IF (@count > 0) -- if table is not empty, Get @Xi from target table Customer
              BEGIN
                SET @lastCustomerid  = (Select Max(customerid) from Customer);
                SET @previousTrackingNo = (Select customerTrackingNo _
                From Customer  where  customerid = @lastCustomerid);
                 SET @previousTrackingNo = (SELECT RIGHT(@previousTrackingNo,7))
                SET @Xi = CAST(@previousTrackingNo as int);
               
              END
    ELSE
              BEGIN
               SET @Xi = @SeedX0; -- if table is empty, take seed @SeedX0 as @Xi = 13
              END
 
    --Get @XiNext from a function based on @Xi value
       SET @customerTrackingNo =dbo.udfGetNextRandomNumber(@Xi);
 
       --Insert record to customer table based on parameters provided
INSERT INTO dbo.Customer(customerTrackingNo, firstName, lastName,sex, [address],isActive)
       Values(@customerTrackingNo, @firstName, @lastName, @sex, @address,@isActive)
END
GO

Points of Interest

Enjoy solving difficult problems related to design and performance improvement on areas Web, Database, Reporting, Analysis, Fuzzy and Machine learning technology.