SQL newid () random function 2

 

Obtain two random records FROM table A and use select top 10 * FROM ywle order by newid ()
Order by is generally sorted by a certain field. The return value of newid () is uniqueidentifier. How does order by newid () randomly select records?
Newid () generates a value when scanning each record, and the generated value is random, with no case sequence. so the final result is sorted by this sort, and the sorting result is of course unordered.
Or
Select top 10 *, newid () as Random from ywle where ywlename = '001' ordey by Random
Lower efficiency
Because newid () returns a unique value of the uniqueidentifier type. The values produced by newid () are different each time. Therefore, the results of each sort are different based on such values.

The principle is to extract all IDs and then use the random function to retrieve one of them, and then use the random obtained ID to retrieve records from the database. The cost is a little high.

 

SQL server's Random Functions newID () and RAND ()

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 uniqueidentifier
SET @ 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.

Obtain two random records FROM table A and use select top 10 * FROM ywle order by newid ()
Order by is generally sorted by a certain field. The return value of newid () is uniqueidentifier. How does order by newid () randomly select records?
Newid () generates a value when scanning each record, and the generated value is random, with no case sequence. so the final result is sorted by this sort, and the sorting result is of course unordered.
Or
Select top 10 *, newid () as Random from ywle where ywlename = '001' ordey by Random
Lower efficiency
Because newid () returns a unique value of the uniqueidentifier type. The values produced by newid () are different each time. Therefore, the results of each sort are different based on such values.

The principle is to extract all IDs and then use the random function to retrieve one of them, and then use the random obtained ID to retrieve records from the database. The cost is a little high.

 

SQL server's Random Functions newID () and RAND ()

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 uniqueidentifier
SET @ 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.

 

From: http://www.cnblogs.com/wenbhappy/archive/2008/05/09/1190603.html