Introduction to the RAND function in SQL Server and implementation of interval random numerical function

    Tasks such as SQL Server simulation data generation and random padding of numeric column values such as Integer, date, and time data types are encountered in the work, and are used to random numbers in these tasks. In view of this, this article will be a simple summary of the use of random numbers in SQL Server.   three functions related to T-SQL Randomization      RAND ([seed]This function generates random from 0 to 1 floatValue (Detailed description view https://technet.microsoft.com/zh-cn/library/ms177610 (v=sql.90). aspx).

CHECKSUM (* | expression [,... n]) This function generates an int checksum value computed by a row or set of expressions in a table, CHECKSUM is used to generate a hash index (detailed description view https:// technet.microsoft.com/zh-cn/library/ms189788 (v=sql.90). aspx).

     NEWID ()This function generates uniqueidentifierA unique value for the type (detailed description view https://technet.microsoft.com/zh-cn/library/ms190348 (v=sql.90). aspx). generate any random number (such as Integer, date, and time data type)       What if I get any random integer value? The result of the function Rand generation is the float data type, which is obviously difficult to meet, but can be demanded by continuing processing of the results of the function rand (for example, its result multiplied by a value to preserve the integer part, etc.). Obviously, the result of the function checksum is the int data type, which is easy to satisfy our results, but the result is the same if the parameter is fixed (the table has a row of the same value or a set of expression values).     The function newid can guarantee the uniqueness of the result, but the result is Unigueidentifer data type. From the results of the above three functions analysis: the function Rnad and checksum results are able to obtain an integer value. If we use the result value of the function newid as the parameter of the function checksum, then the result value of each generation will be a different number of the int data type. The following T-SQL code is as follows: SELECT CHECKSUM (NEWID ()) as Checksumvalue, CHECKSUM (NEWID ()) as CheckSumValue2; The results of the query after go execution are as follows: from the above query results see that the combined generated integer values are 9-bit numbers, usually the most used in the work of random integer values are not too large and are natural numbers (0 and a set of positive integers), which requires the restriction of randomly generated integer values. You can use the function abs to process its results to get any natural number. In this way, the combination of the format is this: ABS (CHECKSUM (NEWID ())). For ease of use we encapsulate it into a function, but the function newid cannot be used in the function, then we have to consider other ways: to encapsulate the function newid in a single-column single-line view. The T-SQL code that defines the view is as follows:

1IF object_id (N'Dbo.vrandomguid','V') is not NULL2 BEGIN3 DROP VIEW dbo.vrandomguid;4 END5 GO6  7--==================================8--function: Random GUID view9--Description: Concrete implementation of the elaborationTen--Author: XXX One--Create: yyyy-mm-DD A--Revision: yyyy-mm-DD XXX Modified content Description ---================================== - CREATE VIEW Dbo.vrandomguid the--$Encode $-- -  as -SELECT Randomguid =NEWID (); - GO +   -Call this view's T-The SQL code is as follows: +SELECT TOP1Randomguid A From Dbo.vrandomguid; atGO

The following query results are executed:

         generate any random integer in an integer range          The above view definition, we continue to explain the further encapsulation of the composite function. We first explain how to limit the randomly generated values, the modulo operation can be generated in a specified range of values, for example: to obtain a range [3,5] any number, we set the minimum value for the interval is @intmin:3, the maximum value is @intmax, then the interval value is @ intmax-@intMin + 1:5-3 + 1 (3), then for any integer value is set to @intvalue, and then the interval value first modulo operation is added to the interval minimum, the last obtained 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 as follows:

1IF object_id (N'Dbo.ufn_randnum','FN') is not NULL2 BEGIN3 DROP FUNCTION dbo.ufn_randnum;4 END5 GO6  7--==================================8--function: Gets any random number in the interval9--Description: Concrete implementation of the elaborationTen--Author: XXX One--Create: yyyy-mm-DD A--Revision: yyyy-mm-DD XXX Modified content Description ---Call: SELECT Dbo.ufn_randnum (0,1); ---================================== the CREATE FUNCTION dbo.ufn_randnum - ( -@intMin INT,--minimum value of a random number -@intMax INT--maximum value of a random number + ) RETURNS INT ---$Encode $-- +  as A BEGIN atSET @intMin = ISNULL (@intMin,0); -SET @intMax = ISNULL (@intMax,0); -   - DECLARE @guidValue as uniqueidentifier; -   -SELECT TOP1@guidValue =Randomguid in From Dbo.vrandomguid; -   toRETURN ABS (CHECKSUM (@guidValue))% (@intMax-@intMin +1) +@intMin; + END -GO

The T-SQL code that calls the above function is as follows:

1 SELECT dbo.ufn_randnum (01) as Randnum, Dbo.ufn_randnum (ten) as RandNum2; 2 GO

The following query results are executed: generate any random date in a date rangeSo we'll go on. Gets any random date within the specified interval, but we want to use the date and time of the two functions: DateAdd and DateDiff. Use DateDiff to calculate the interval value of the date interval, the same idea we calculated a random integer value, and then through the DATEADD to get our expectation of a random date. The T-SQL code for the encapsulated function is as follows:

1IF object_id (N'dbo.ufn_randdate','FN') is not NULL2 BEGIN3 DROP FUNCTION dbo.ufn_randdate;4 END5 GO6  7--==================================8--function: Gets any random date in the date range9--Description: Concrete implementation of the elaborationTen--Author: XXX One--Create: yyyy-mm-DD A--Revision: yyyy-mm-DD XXX Modified content Description ---Call: SELECT @dtmRand = Dbo.ufn_randdate ('2007-02-01','2007-03-01'); ---================================== the CREATE FUNCTION dbo.ufn_randdate - ( -@dtmMin DATETIME,--minimum value for random date -@dtmMax DATETIME--maximum value of a random date + ) RETURNS DATETIME ---$Encode $-- +  as A BEGIN atSET @dtmMin = ISNULL (@dtmMin,'2000-01-01'); -SET @dtmMax = ISNULL (@dtmMax,'2000-01-01'); - DECLARE @guidVue as uniqueidentifier; -   -SELECT TOP1@guidVue =Randomguid - From Dbo.vrandomguid; in   ---You can change the hour to days toRETURN DATEADD (HOUR, (ABS (CHECKSUM (@guidVue))% (1+DATEDIFF (HOUR, @dtmMax, @dtmMin))), @dtmMin); + END -GO

The T-SQL code that calls the above function is as follows:

1 SELECT dbo.ufn_randdate ('2015-12-01'2015-12-21' as Randdate, dbo.ufn_randdate ('2016-12-01'2016-12-21 ' ) as RandDate2; 2 GO

The following query results are executed:

the extended thinking of interval random number acquisitionWe use the view vrandomguid whether to get random values within a range of values or a random date within a date range.    If there is a sequential numerical sequence, we can also achieve the above results by querying the numerical sequence, combined with our interval values and random ordering. Assuming that the numeric sequence is seqdatatable (Num INT), the numeric interval [@intMin, @intMax], the T-SQL code for any random number in that range is as follows:

1 1 Num 2 From dbo. Seqdatatable3WHERE Num between @intMin and @intMax4 ORDER by NEWID () ASC;

Of course, you can also take advantage of the above numeric sequence seqdatatable (Num INT), date range [@dtmMin, @dtmMax], the T-SQL code for any random date in that number range:

1 1 DATEADD (Day, Num, @dtmMin) 2 From dbo. Seqdatatable30  and DATEDIFF (Day, @dtmMax, @dtmMin)4 ORDER by NEWID () ASC;

Note: The above code cannot be executed successfully, it is also pseudo-code.

      In the future there will be a detailed explanation of the sequence of numbers, here for the moment as an extension to think about it. List of reference listshttps://technet.microsoft.com/zh-cn/library/ms177563 (v=sql.90). aspxhttps://technet.microsoft.com/zh-cn/library /ms177610 (v=sql.90). aspxhttps://technet.microsoft.com/zh-cn/library/ms189788 (v=sql.90). aspxhttps:// technet.microsoft.com/zh-cn/library/ms190348 (v=sql.90). aspxhttp://www.cnblogs.com/chenxizhang/archive/2009/06/ 26/1511898.html bo Friends If there are other better solutions, please advise, thank you very much.

Introduction to the RAND function in SQL Server and implementation of interval random numerical function