The Random Functions NewID (), Rand (), newidrand in SQL server

The Random Functions NewID (), Rand (), newidrand in SQL server

In SQL Server, random functions include rand (), NewID (), where rand is a random number from 0 to 1, and NewID is the unique identifier of the random uniqueidentifier generated.

SELECT * FROM Northwind... Orders order by newid ()
-- Random sorting

Select top 10 * FROM Northwind .. Orders order by newid ()
-- Randomly retrieve 10 records from the Orders table

Example

A. Use the NEWID function for variables

The following example uses NEWID () to assign values to variables declared as uniqueidentifier data types. Output this value before testing the value of the uniqueidentifier data type variable.

-- Creating a local variable with DECLARESET syntax.DECLARE @myid uniqueidentifierSET @myid = NEWID()PRINT 'Value of @myid is '+ CONVERT(varchar(255), @myid)

The following is the result set:

Value of @ myid is 6F9619FF-8B86-D011-B42D-00C04FC964FF

Note:

NEWID returns different values for each computer. The displayed number only serves as an explanation.

Random Function: rand ()

Run select rand () in the query analyzer and you can see that the result is a random decimal like this: 0.36361513486289558. decimal places like this are not used much in practical applications, generally, random numbers are random integers. Let's look at the following two random integer acquisition methods:

1,

A: select floor (rand () * N) --- The generated number is as follows: 12.0

B: select cast (floor (rand () * N) as int) --- The generated number is as follows: 12

2,

A: select ceiling (rand () * N) --- The generated number is as follows: 12.0

B: select cast (ceiling (rand () * N) as int) --- The generated number is as follows: 12

The N in it is an integer you specify, such as 100. We can see that the method of the two methods contains. the decimal value of 0, and the B method is the real integer.

Generally, there is no difference between the two methods? There is actually one thing, that is, their range of random numbers:

The number range of Method 1: 0 to N-1, for example cast (floor (rand () * 100) as int) will generate any integer between 0 and 99

The number range of Method 2: 1 to N. For example, cast (ceiling (rand () * 100) as int) generates any integer between 1 and 100.

For this difference, you can see the online help of SQL:
Bytes ------------------------------------------------------------------------------------

Compare CEILING and FLOOR

The CEILING function returns the smallest integer greater than or equal to the given numeric expression. The FLOOR function returns the largest integer less than or equal to the given numeric expression. For example, for the numeric expression 12.9273, CEILING returns 13 and FLOOR returns 12. The data types returned by FLOOR and CEILING are the same as those of the input numeric expression.
----------------------------------------------------------------------------------
Now, you can use these two methods as needed to obtain the random number ^_^.

In addition, I would like to remind you that the method for randomly obtaining any N records in the table is very simple, just use newid ():

Select top N * from table_name order by newid () ---- N is a specified integer, and the table is the number of records obtained.