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

You will encounter tasks such as SQL Server mock data Generation and random padding of numeric column values (such as Integer, date, and time data types), all of which use random numbers. For this reason, this article will briefly summarize the use of random numbers in SQL Server.

T-SQL random related three functions

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

CHECKSUM (* | expression [,... n]) This function generates an int checksum value calculated as a row or a 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 a unique value for the uniqueidentifier type (Details view https://technet.microsoft.com/zh-cn/library/ms190348 (v=sql.90). aspx).

Generate any random number (such as Integer, date, and time data types)

If you get any random integer value? The result of the function Rand generation is a float data type, which is clearly difficult to meet, but can be requested by continuing the processing of the result of the function rand (such as its result multiplied by a value to preserve the integer part, and so on). Obviously, the result of a function checksum is an int data type, and it's easy to meet our results, but if the arguments are fixed (the table has the same row value or a set of expression values), the result is the same. function newid can guarantee the uniqueness of the result, but the result is Unigueidentifer data type.

The result values of the above three functions are analyzed: The result of the function Rnad and checksum is that the integer value can be obtained. If we use the result value of the function newid as the parameter of the function checksum, the resulting value of each generation is a numeric value of the different int data type. The following T-SQL code is as follows:

SELECT CHECKSUM (NEWID ()) as Checksumvalue, CHECKSUM (NEWID ()) as CheckSumValue2;
Go

The results of the executed query are as follows:

From the above query results to see the combination generated integer values are 9 digits, usually the work of the most used random integer values are not too large and are natural numbers (0 and positive integer set), which requires limiting randomly generated integer values. The function abs can be used to process the results of any one natural number. In this way, the combination of a form is this: ABS (CHECKSUM (NEWID)). In order to be easy to use we encapsulate it into a function, but the function newid can not be used in the function, then we have to consider the other way: the function newid encapsulated in a single row of the view. 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: Concrete implementation Elaboration- 
 -Author: XXX
 --Create: YYYY-MM-DD
 --Modification: YYYY-MM-DD XXX Modify Content Description
 --==================================
 CREATE VIEW Dbo.vrandomguid
   --$Encode $--
 as
   SELECT randomguid = NEWID ();
 Go
 calls the T-SQL code for this view as follows:
 SELECT top randomguid from
 Dbo.vrandomguid;
 

The results of the executed query are as follows:

Generate any random integer within an integer interval

The view definition above, we continue to explain the further encapsulation of the composite function. Let's start by explaining how to limit randomly generated values, the modulo operation can generate any number in the specified range of values, for example: get any number in the interval [3,5], we set the minimum value of the interval to @intmin:3, the maximum is @intmax, then the interval value of the interval is @ intmax-@intMin + 1:5-3 + 1 (3), for any integer value set to @intvalue, and then the interval value of the first modulo operation in addition to the interval minimum value, the resulting value is: @intValue% (@intMax-@intMin + 1) + @intMin. If the @intvalue is 8, the result value is 5, and 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:

 IF object_id (N ' dbo.ufn_randnum ', ' FN ') is not NULL
 BEGIN
   DROP FUNCTION dbo.ufn_randnum;
 End
 Go
 --==================================
 -function: Get any random number within the interval-
 -Description: Specific implementation- 
 -Author: XXX
 - -Create: Yyyy-mm-dd
 --Modification: YYYY-MM-DD XXX Modify Content Description
 --Call: SELECT Dbo.ufn_randnum (,);
 --==================================
 CREATE FUNCTION dbo.ufn_randnum
 (
   @intMin INT,      -- The minimum value of a random number
   @intMax INT-        -the maximum value of a random number
 ) 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 that calls the above function is as follows:

 SELECT Dbo.ufn_randnum (,) as Randnum, Dbo.ufn_randnum (,) as Randnum;
 

The results of the executed query are as follows:

Generate any random date within the date range

So let's go on to get any random date within the specified range, but we're going to use the two functions of the date and time: DateAdd and DateDiff. Using DateDiff to calculate the interval value of the date interval, above the idea we calculated a random integer value, and then through the DateAdd to get a random date we expect. 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: Get any random date in the date range-
 -Description: Specific implementation- 
 -Author: XXX -
 -Create: Yyyy-mm-dd
 --Modify: Yyyy-mm-dd XXX Modify Content Description
 --Call: SELECT @dtmRand = dbo.ufn_randdate ('--', ', '-');
 --==================================
 CREATE FUNCTION dbo.ufn_randdate 
 (
   @dtmMin DATETIME,  -- Minimum
   @dtmMax datetime  --Maximum of random dates--
 RETURNS datetime-
   -$Encode $--as
 BEGIN
   SET @dtmMin = ISNULL (@dtmMin, '--');
   SET @dtmMax = ISNULL (@dtmMax, '--');
   DECLARE @guidVue as uniqueidentifier;
   SELECT Top @guidVue = Randomguid from 
   dbo.vrandomguid;
   --You can change the HOUR to
   DATEADD (HOUR, ABS (CHECKSUM (@guidVue))% (+ DATEDIFF (HOUR, @dtmMax, @dtmMin)), @dtmMin); C30/>end
 

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

 SELECT dbo.ufn_randdate ('--', '--') as Randdate, Dbo.ufn_randdate ('--', '--') as randdate;
 

The results of the executed query are as follows:

Extended thinking of interval random number acquisition

We use the view vrandomguid either to get a random number within a range or a random date within a date range. If there is a continuous numerical sequence, we can also achieve the above results by querying the numerical sequence, combining our interval values and random ordering.

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

 SELECT top Num from
 dbo. Seqdatatable
 WHERE Num BETWEEN @intMin and @intMax
 

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

 SELECT Top DATEADD (day, Num, @dtmMin) from
 dbo. Seqdatatable
 WHERE Num BETWEEN and DATEDIFF (Day, @dtmMax, @dtmMin)
 

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