Introduction to the RAND function in SQL Server and Implementation of the interval random numeric function,

Introduction to the RAND function in SQL Server and Implementation of the interval random numeric function,

During work, you will encounter tasks such as SQL Server simulated data generation and random filling of numeric column values (such as integer, date, and time data types). Random numbers are used in these tasks. In view of this, this article will summarize the use of random numbers in SQL Server.

Three functions related to random T-SQL

RAND ([seed] This function generates a random float value from 0 to 1 (view the https://technet.microsoft.com/zh-cn/library/ms177610 for details (v = SQL .90). aspx ).

CHECKSUM (* | expression [,... n]) This function generates the int validation and value calculated based on a row or a group of expressions in the table, CHECKSUM is used to generate a hash index (view https://technet.microsoft.com/zh-cn/library/ms189788 for details (v = SQL .90 ). aspx ).

NEWID () This function generates a unique value of the uniqueidentifier type (detailed description view https://technet.microsoft.com/zh-cn/library/ms190348 (v = SQL .90). aspx ).

Generate any random value (such as integer, date, and time data type)

What if we get any random integer? The result generated by the function RAND is of the float data type, which is obviously difficult to meet the requirements. However, you can continue to process the result of the function RAND (for example, multiply the result by a value to retain the integer part) obtain the requirement. Obviously, the result generated by the function CHECKSUM is of the int data type, which can easily satisfy our results. However, if the parameter is fixed (the values of a row in the table are the same or the values of a group of expressions are the same ), the results are the same. The NEWID function ensures that the result is unique, but the result is of the unigueidentifer data type.

From the analysis of the result values of the above three functions: The result of the RNAD and CHECKSUM functions can obtain integer values. If we use the result value of the function NEWID as the parameter of the function CHECKSUM, The result value generated each time is a different int value. The following T-SQL code is as follows:

SELECT CHECKSUM(NEWID()) AS CheckSumValue, CHECKSUM(NEWID()) AS CheckSumValue2;GO

The query result after execution is as follows:

 

From the preceding query results, we can see that the integer values generated by the combination are all nine digits. Generally, the random integer values used in normal work are not very large and they are all natural numbers (a set of 0 and positive integers ), this requires limiting random integer values. You can use the ABS function to process the result to obtain any natural number. In this way, the format of the combination is as follows: ABS (CHECKSUM (NEWID ())). For ease of use, we encapsulate it into the function, but the NEWID function cannot be used in the function, so we need to consider other methods: encapsulate the NEWID function in the view of a single row. The T-SQL code for its definition view is as follows:

IF OBJECT_ID (n' dbo. vRandomGuid ', 'V') is not null begin drop view dbo. vRandomGuid; end go -- ======================================================== -- function: random Guid View -- Description: Implementation description -- Author: XXX -- create: yyyy-MM-dd -- modify: description of yyyy-MM-dd XXX modification -- ============================== ==== create view dbo. vRandomGuid -- $ Encode $ -- as select RandomGuid = NEWID (); GO calls the view with the following T-SQL code: select top RandomGuid FROM dbo. vRandomGuid; GO

The query result after execution is as follows:

Generates any random integer in the Integer Range.

In the preceding view definition, we will continue to explain the further encapsulation of composite functions. First, we will explain how to limit randomly generated values. The modulo operation can generate any number within the specified value range. For example, you can obtain any number within the range [3, 5, we set the minimum value of the interval to @ intMin: 3 and the maximum value to @ intMax. The interval value of this interval is @ intMax-@ intMin + 1: 5-3 + 1 (3), set any integer to @ intValue, and then perform the modulo operation on the value of the interval to add the minimum value of the interval, the final value is @ intValue % (@ intMax-@ intMin + 1) + @ intMin. If @ intValue is 8, The result value is 5. If @ intValue is 9, The result value is 3. If @ intValue is 10, the result value is 4 ......

For the above analysis we encapsulate the T-SQL code is as follows:

IF OBJECT_ID (n' dbo. ufn_RandNum ', 'fn') is not null begin drop function dbo. ufn_RandNum; end go -- ======================================================== -- function: obtain any random value in the interval -- Description: Implementation description -- Author: XXX -- create: yyyy-MM-dd -- modify: description of modification content in yyyy-MM-dd XXX -- call: SELECT dbo. ufn_RandNum (,); -- ====================================== create function dbo. ufn_RandNum (@ intMin INT, -- minimum value of a random value @ intMax INT -- maximum value of a random value) returns int -- $ Encode $ -- as begin set @ intMin = ISNULL (@ intMin ,); SET @ intMax = ISNULL (@ intMax,); DECLARE @ guidValue as uniqueidentifier; select top @ guidValue = RandomGuid FROM dbo. vRandomGuid; return abs (CHECKSUM (@ guidValue) % (@ intMax-@ intMin +) + @ intMin; END GO

The T-SQL code for calling the above function is as follows:

 SELECT dbo.ufn_RandNum(, ) AS RandNum, dbo.ufn_RandNum(, ) AS RandNum; GO 

The query result after execution is as follows:

 

Generate any random date in the date range

We will continue to explain how to obtain any random date in the specified range, but we need to use two functions of date and time: DATEADD and DATEDIFF. Use DATEDIFF to calculate the interval value of the date range. In the same idea, we obtain a random integer, and then use DATEADD to obtain the expected random date. The T-SQL code for the encapsulated function is as follows:

IF OBJECT_ID (n' dbo. ufn_RandDate ', 'fn') is not null begin drop function dbo. ufn_RandDate; end go -- ======================================================== -- function: obtain any random date in the date range -- Description: Implementation description -- Author: XXX -- create: yyyy-MM-dd -- modify: modify description of yyyy-MM-dd XXX -- call: SELECT @ dtmRand = dbo. ufn_RandDate ('--','--'); -- ====================================== create function dbo. ufn_RandDate (@ dtmMin DATETIME, -- minimum value of a random date @ dtmMax DATETIME -- maximum value of a random date) returns datetime -- $ Encode $ -- as begin set @ dtmMin = ISNULL (@ dtmMin, '--'); SET @ dtmMax = ISNULL (@ dtmMax, '--'); DECLARE @ guidVue as uniqueidentifier; select top @ guidVue = RandomGuid FROM dbo. vRandomGuid; -- replace HOUR with days return dateadd (HOUR, (ABS (CHECKSUM (@ guidVue) % (+ DATEDIFF (HOUR, @ dtmMax, @ dtmMin ))), @ dtmMin); END GO

The T-SQL code for calling the above function is as follows:

 SELECT dbo.ufn_RandDate('--', '--') AS RandDate, dbo.ufn_RandDate('--', '--') AS RandDate; GO 

The query result after execution is as follows:

 

Expansion of random number acquisition in intervals

The view vRandomGuid is used to obtain the random values in the value range or the random date in the date range. If there is a continuous numerical sequence, we can query this numerical sequence and combine our range values and random sorting to achieve the above results.

Assuming that the numerical sequence is SeqDataTable (Num INT), the numerical range [@ intMin, @ intMax], then the T-SQL code of any random numerical value in the numerical range is as follows:

 SELECT TOP Num FROM dbo.SeqDataTable WHERE Num BETWEEN @intMin AND @intMax ORDER BY NEWID() ASC; 

Of course, you can also use the above numerical sequence SeqDataTable (Num INT), date range [@ dtmMin, @ dtmMax], the T-SQL code of any random date in the numerical range is as follows:

 SELECT TOP DATEADD(DAY, Num, @dtmMin) FROM dbo.SeqDataTable WHERE Num BETWEEN AND DATEDIFF(DAY, @dtmMax, @dtmMin) ORDER BY NEWID() ASC; 

Note: If the above Code cannot be successfully executed, it is also a pseudo-code.

Articles you may be interested in:

• Javascript Math. random () random Number Function
• Shuffle () and array_rand () Random Functions in php Array Function Sequence
• Dbms_random usage of Oracle Random Functions
• Comparison between functions rand and mt_rand in PHP
• Usage of the rand function for generating random numbers
• Random Number generated by js: random example of the random function
• Php uses the array_rand () function to randomly select one or more elements from the array.
• Use the js Math. random () function to generate random numbers between n and m.