Introduction to the random number function rand () in SQL

Transferred from: http://database.51cto.com/art/201009/224397.htm

The following will introduce you to the random function rand () in SQL, for your reference, if you are new to SQL Server, you might want to look at it and believe it will help you learn about the functions in SQL.

In SQL Server, there is a random function rand (), some novice may not know the existence of this function, and now I will be the function of some random number generation techniques to write, this is for the rookie, the veteran, please do not shoot bricks, my head is not hard enough
But still hope that the veteran have a lot of advice, now cut to the point:
Random function: rand ()
Execute in Query Analyzer: Select rand (), you can see that the result would be a random decimal like this: 0.36361513486289558,
Decimals like this are not used much in practical applications, and random numbers are usually taken as a random number. Let's look at the following two methods of randomly taking integers:
1.
A:
The number---generated by select Floor (rand () *n) is like 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.

Introduction to the random number function rand () in SQL