Random functions newid() and rand() in SQL Server

Time:2020-10-23

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()
–Random sorting

SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID()
–Take 10 records randomly from the orders table

Examples

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

be careful:

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:

1、

A:select  floor(rand()*N) —The generated number is like this: 12.0

B:select cast( floor(rand()*N) as int) —The number generated is like this: 12

2、

A:select ceiling(rand() * N) —The generated number is like this: 12.0

B: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

Recommended Today

Comparison and analysis of Py = > redis and python operation redis syntax

preface R: For redis cli P: Redis for Python get ready pip install redis pool = redis.ConnectionPool(host=’39.107.86.223′, port=6379, db=1) redis = redis.Redis(connection_pool=pool) Redis. All commands I have omitted all the following commands. If there are conflicts with Python built-in functions, I will add redis Global command Dbsize (number of returned keys) R: dbsize P: print(redis.dbsize()) […]