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:
|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
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:
- 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: 2018-08-07
About the author
View all my tips