Go SQL NEWID () random function

Randomly fetch 2 records from a table, using select TOP * from Ywle ORDER by NEWID ()
Order by is generally sorted by a field, the return value of newid () is uniqueidentifier, and the Order by NEWID () random selection record is performed
NEWID () generates a value when each record is scanned, and the resulting values are random, with no case order. So the final result is sorted by this sort, and the result of the order is, of course, unordered.
Or
Select Top *,newid () as random from Ywle where ywlename= ' 001 ' Ordey by Random
The efficiency of the next person is higher.
Because NEWID () returns a unique value of type uniqueidentifier. NEWID () each time the value is different, then the order is sorted according to such value, each time the result is not the same.

The principle is to take all the IDs out and then use the random function to get one, and then use the random ID to go to the database and then take out the records, all the cost is a bit large.

Random functions of SQL Server NEWID () and Rand ()

SELECT * from Northwind. Orders ORDER by NEWID ()
--Random sorting

SELECT TOP Ten * from Northwind. Orders ORDER by NEWID ()
--Randomly remove 10 records from the Orders table

Example

A. Using the NEWID function with variables
The following example uses NEWID () to assign a value to a variable declared as a uniqueidentifier data type. The value is output 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)
Here is the result set:
Value of @myid is 6f9619ff-8b86-d011-b42d-00c04fc964ff
Attention:
NEWID the values returned for each computer are different. The figures shown are only useful for interpreting.

Random function: rand ()
Execute in Query Analyzer: Select rand (), you can see that the result is similar to a random decimal: 0.36361513486289558, a decimal like this is used in the actual application is not much, generally to take random numbers will take random integers. Let's look at the following two methods of randomly taking integers:

1.
A:select Floor (rand () *n)---Number generated is this: 12.0
B:select cast (*n) as int---the number generated is this: 12

2.
A:select Ceiling (rand () * N)---Generated number is this: 12.0
B:select cast (Ceiling (rand () * N) as int)---the number generated is this: 12

where n is an integer you specify, such as 100, you can see that the a method of the two methods is a decimal with the. 0, and the B method is the real integer.
In general, there is no difference between the two methods, really no difference? In fact, there is a point where they generate a random number range:
The numeric range of Method 1: between 0 and N-1, such as CAST (Floor (rand () *100) as int) generates any integer from 0 to 99
The numeric range of Method 2: between 1 and N, such as cast (ceiling () as int) generates any integer from 1 to 100
For this difference, look at the SQL online Help on the cicada:
------------------------------------------------------------------------------------

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 number expression. For example, for a numeric expression 12.9273,ceiling will return 13,floor will return 12. Floor and CEILING The data type of the return value is the same as the data type of the input numeric expression.
----------------------------------------------------------------------------------
Now, you can use these two methods to get the random number according to your own needs ^_^

In addition, we also need to remind you rookie, about random access to the table of arbitrary N Records of the method, very simple, with newid ():
Select TOP N * FROM table_name ORDER BY NEWID ()----N is an integer that you specify, and the table is the number of entries that are obtained for the record.

Randomly fetch 2 records from a table, using select TOP * from Ywle ORDER by NEWID ()
Order by is generally sorted by a field, the return value of newid () is uniqueidentifier, and the Order by NEWID () random selection record is performed
NEWID () generates a value when each record is scanned, and the resulting values are random, with no case order. So the final result is sorted by this sort, and the result of the order is, of course, unordered.
Or
Select Top *,newid () as random from Ywle where ywlename= ' 001 ' Ordey by Random
The efficiency of the next person is higher.
Because NEWID () returns a unique value of type uniqueidentifier. NEWID () each time the value is different, then the order is sorted according to such value, each time the result is not the same.

The principle is to take all the IDs out and then use the random function to get one, and then use the random ID to go to the database and then take out the records, all the cost is a bit large.

Random functions of SQL Server NEWID () and Rand ()

SELECT * from Northwind. Orders ORDER by NEWID ()
--Random sorting

SELECT TOP Ten * from Northwind. Orders ORDER by NEWID ()
--Randomly remove 10 records from the Orders table

Example

A. Using the NEWID function with variables
The following example uses NEWID () to assign a value to a variable declared as a uniqueidentifier data type. The value is output 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)
Here is the result set:
Value of @myid is 6f9619ff-8b86-d011-b42d-00c04fc964ff
Attention:
NEWID the values returned for each computer are different. The figures shown are only useful for interpreting.

Random function: rand ()
Execute in Query Analyzer: Select rand (), you can see that the result is similar to a random decimal: 0.36361513486289558, a decimal like this is used in the actual application is not much, generally to take random numbers will take random integers. Let's look at the following two methods of randomly taking integers:

1.
A:select Floor (rand () *n)---Number generated is this: 12.0
B:select cast (*n) as int---the number generated is this: 12

2.
A:select Ceiling (rand () * N)---Generated number is this: 12.0
B:select cast (Ceiling (rand () * N) as int)---the number generated is this: 12

where n is an integer you specify, such as 100, you can see that the a method of the two methods is a decimal with the. 0, and the B method is the real integer.
In general, there is no difference between the two methods, really no difference? In fact, there is a point where they generate a random number range:
The numeric range of Method 1: between 0 and N-1, such as CAST (Floor (rand () *100) as int) generates any integer from 0 to 99
The numeric range of Method 2: between 1 and N, such as cast (ceiling () as int) generates any integer from 1 to 100
For this difference, look at the SQL online Help on the cicada:
------------------------------------------------------------------------------------

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 number expression. For example, for a numeric expression 12.9273,ceiling will return 13,floor will return 12. Floor and CEILING The data type of the return value is the same as the data type of the input numeric expression.
----------------------------------------------------------------------------------
Now, you can use these two methods to get the random number according to your own needs ^_^

In addition, we also need to remind you rookie, about random access to the table of arbitrary N Records of the method, very simple, with newid ():
Select TOP N * FROM table_name ORDER BY NEWID ()----N is an integer that you specify, and the table is the number of entries that are obtained for the record.