Random functions in SQL Server NEWID () and rand () _mssql

In SQL Server, random functions have rand (), NewID (), where Rand is randomly fetched in 0 to 1, and NewID is a random uniqueidentifier unique identifier.

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

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

Example

A. Using NEWID functions on variables

The following example uses NEWID () to assign a value to a variable declared to be a uniqueidentifier data type. The value is output before the value of the uniqueidentifier data type variable is tested.

--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 by each computer are different. The figures shown are only explanations.

Random function: rand ()

Executing in Query Analyzer: Select rand (), you can see the result will be similar to the random decimal: 0.36361513486289558, such as decimals in the actual application is not much, the general to take random numbers will take random integers. Look at the following two methods of randomly taking integers:

1,

The number of A:select floor (rand () *n)---generated is this: 12.0

B:select CAST (Floor (rand () *n) as int)---the number generated is this: 12

2,

The number of A:select ceiling (rand () * N)---Generated is this: 12.0

B:select cast (Ceiling (rand () * N) as int)---the number generated is this: 12

Where the n is an integer you specify, such as 100, you can see that the a method of the two methods is a decimal with a. 0, and the B method is a real integer.

Generally speaking, the two methods are no different, really no difference? There's a little bit of that, that's the range of their generated random numbers:

The number range of Method 1:0 through N-1, such as CAST (Floor (rand () *100) as int), generates any integer between 0 and 99

Method 2 has a range of digits between 1 and N, such as cast (ceiling rand () () as int), which generates any integer between 1 and 100

For this distinction, look at SQL's online Help for a cicada:
------------------------------------------------------------------------------------

Compare CEILING and FLOOR

The CEILING function returns the smallest integer that is 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 numeric expression 12.9273,ceiling will return 13,floor will return 12. The data types of the FLOOR and CEILING return values are the same as the data types of the numeric expressions entered.
----------------------------------------------------------------------------------
Now, you can use these two methods to get a random number of ^_^

In addition, but also to remind you rookie, about random access to any of the table N Records of the method, very simple, with newid ():

Select Top N * NEWID ()----n is an integer that you specify, and the table is the number of table_name to get the record.