未加星标

SQL Server Function to Generate Random Numbers

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏
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:

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


SQL Server Function to Generate Random Numbers
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: 2018-08-07


SQL Server Function to Generate Random Numbers
SQL Server Function to Generate Random Numbers
About the author
SQL Server Function to Generate Random Numbers
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience. View all my tips

Related Resources

More Database Developer Tips...

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

tags: function,rand,SQL,random,method,Val,GenRandomNumber,number,built,time
分页:12
转载请注明
本文标题:SQL Server Function to Generate Random Numbers
本站链接:https://www.codesec.net/view/586279.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(39)