By: Eli Leiba | Updated: 2018-08-07 | Comments (1) | Related: > 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:
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.
About the author
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
Article Last Updated: 2018-08-07
