In SQL server, the random functions are rand (), newid (), where Rand is random access from 0 to 1, and newid is the unique identifier that generates random uniqueidentifier.
SELECT * FROM Northwind..Orders ORDER BY NEWID()
SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID()
–Take 10 records randomly from the orders table
A. Use the newid function on variables
The following example uses newid() to assign a value to a variable declared as a uniqueidentifier data type. Before testing the value of the uniqueidentifier data type variable, output the value.
-- 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
Newid returns different values for each computer. The figures shown are for explanatory purposes only.
Random function: rand ()
Execute in query analyzer:
select rand()It can be seen that the result will be a random decimal like this: 0.36361513486289558. Such decimals are rarely used in practical applications. Generally, random integers will be taken if random numbers are to be taken. Let’s look at the following two random integer methods:
select floor(rand()*N) —The generated number is like this: 12.0
select cast( floor(rand()*N) as int) —The number generated is like this: 12
select ceiling(rand() * N) —The generated number is like this: 12.0
select cast(ceiling(rand() * N) as int) —The number generated is like this: 12
Among them, n is an integer you specify, such as 100. You can see that method a of the two methods has a decimal of. 0, while method B is the real integer.
Generally speaking, there is no difference between the two methods. Is there really no difference? In fact, there is a point, that is, the range of their generated random numbers:
The number range of method 1: 0 to n-1, such as cast (floor (rand() * 100) as int) will generate any integer between 0 and 99
The number range of method 2 is between 1 and N. for example, cast (ceiling (rand() * 100) as int) will generate any integer between 1 and 100
For this difference, see the SQL online help
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 will return 13 and floor will return 12. Both floor and ceiling return values have the same data type as the numeric expression entered.
Now, you can use these two methods to get random numbers according to your needs^_^
In addition, I would like to remind you that the method of randomly obtaining any n records in the table is very simple. Use newid():
select top N * from table_name order by newid()—-N is an integer you specify, and the table is the number of records