Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools