CLOUD SQL 

SQL Server Function to Generate Random Numbers



By:   |   Read Comments   |   Related Tips: More > T-SQL







Problem

The request is to expand upon the SQL Server randomizing capabilities by creating
a function that will generate a random number in many different ways, thus enabling
the user to choose the randomizing generation approach from four different methods. 

Solution

My solution involves creating a T-SQL function in the SQL Server master database,
called GenRandomNumber.

This function gets a parameter called @method that defines the
method of generating the random number.

The @method parameter can be one of these four possibilities:







Method Value Description
rand Method uses the SQL built-in RAND() function to generate the random
number
crypt Method uses the SQL built-in CRYPT_GEN_RANDOM () function to generate
the random number
newid Method uses the SQL built-in NEWID() GUID built-in function generator
to generate the random number
time Method uses a combination of the DATEPART() and GETDATE() time function
and RAND() function to generate the random number

The function uses a CASE statement query that queries a pre-defined view that
generates all possible random numbers as described in the table above. The resulting
random number will be rounded to 6 digits precision.

The view is necessary because you cannot call a non-deterministic function inside
of a user-defined function. If you try doing this, for example with the using the
rand() function directly, you will encounter the following error message:

Msg 443, Level 16, State 1, Procedure GenRandomNumber, Line 24
Invalid use of a side-effecting operator 'rand' within a function.

In order to overcome this limitation, you create a view containing the call to
the non-deterministic functions.

Here is the T-SQL code for the to create the view:

USE master
GO
CREATE VIEW VW_Random
AS
SELECT 
   RAND () AS Val_Rand,
   NEWID () AS Val_NewId,
   ROUND (RAND ((DATEPART (mm, GETDATE ()) * 100000) + (DATEPART (ss, GETDATE ()) * 1000) + DATEPART (ms, GETDATE ())), 6) AS Val_Time,
   CRYPT_GEN_RANDOM (3) AS Val_crypt
GO

SQL Server Function to Generate Random Numbers

USE master
GO

CREATE FUNCTION dbo.GenRandomNumber (@method VARCHAR (5))
RETURNS DECIMAL (8, 6)
   -- ================================================================
   -- Author:      Eli Leiba
   -- Create date: 07-2018
   -- Function Name: dbo.GenRandomNumber
   -- Description:
   --   Generates a random number between 0 and 1 (3 digits) 
   --   supports four methods (@method is input parameter)
   --    1) 'rand' = using SQL built-in rand () function.
   --    2) 'newid' = using NEWID GUID generator.
   --    3) 'time' = using a time and rand built-in combination.
   --    4) 'crypt' = using the built-in CRYPT_GEN_RANDOM function.
   -- ================================================================
AS
BEGIN
   DECLARE @rand DECIMAL (8, 6)

   SELECT @rand = CASE lower (@method)
         WHEN 'rand'
            THEN ROUND (Val_Rand, 6)
         WHEN 'newid'
            THEN 0.000001 * ABS (CAST (Val_NewId AS BINARY (6)) % 1000000)
         WHEN 'time'
            THEN Val_Time
         WHEN 'crypt'
            THEN 0.000001 * ABS (CAST (Val_crypt AS BINARY (6)) % 1000000)
         ELSE ROUND (Val_Rand, 6)
         END
   FROM VW_Random

   RETURN @rand
END
GO

Sample Execution

USE master
GO

SELECT dbo.GenRandomNumber ('newid') as method_by_newid_rand,
       dbo.GenRandomNumber ('rand')  as method_by_rand_rand,
       dbo.GenRandomNumber ('crypt') as method_by_crypt_rand,
       dbo.GenRandomNumber ('time')  as method_by_time_rand
GO

And the results are as follows:

query results

Next Steps

  • You can create and compile this simple function as a UDF in your application
    or master database and use it as a simple TSQL tool for generating random numbers
    in all the four different methods.
  • The stored procedure was tested with SQL Server 2014, but should work with
    all versions.


Last Update:


next webcast button

next tip button

About the author

MSSQLTips author Eli Leiba

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips


Related posts

Leave a Comment