SQL Server Function to Generate Random Numbers

By:   |   Comments (1)   |   Related: > TSQL


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. 


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
   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

SQL Server Function to Generate Random Numbers

USE master

CREATE FUNCTION dbo.GenRandomNumber (@method VARCHAR (5))
   -- ================================================================
   -- 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.
   -- ================================================================
   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)
   FROM VW_Random

   RETURN @rand

Sample Execution

USE master

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

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

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.

get free sql tips
agree to terms