SQL Server Function to Generate Random Numbers

By:   |   Updated: 2018-08-07   |   Comments (1)   |   Related: More > TSQL


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.



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

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


Article Last Updated: 2018-08-07

Comments For This Article




Friday, October 30, 2020 - 8:48:10 AM - John Delahunt Back To Top (86724)
If you extend the test statement to call each method 3 times you will notice that the rand method and the time method return the same value all three times. It looks like the NewID and crypt methods are much more random. Thanks for showing all these methods together in a single function.


download














get free sql tips
agree to terms