SQL Server random number, random interval, randomly extracted data rand (), floor (), ceiling (), round (), newid () function, etc.

Source: Internet
Author: User
Tags floor function

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:
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 records obtained

3,

ROUND () function
The ROUND function is used to round a numeric field to a specified number of decimal digits.
SQL ROUND () syntax
SELECT ROUND (column_name,decimals) from table_name

Parameter description
column_name required. The field to be rounded.
Decimals required. Specifies the number of decimal digits to return.

For example:

Select ROUND (15.258, 1) The result is: 15.300

You can use the round () function with the rand () function to randomly generate a specified interval:

DECLARE @Result INTDECLARE @Upper INTDECLARE @Lower INTSET @Lower = 1SET @Upper = TenSELECT @Result = ROUND(((@Upper - @Lower -1)* RAND()+ @Lower),0)SELECT @Result

The above code gets a number within the [@Lower, @Upper) interval, that is, the minimum is @lower, and the maximum is the random number between @upper-1.

SQL Server random number, random interval, randomly extracted data rand (), floor (), ceiling (), round (), newid () function, etc.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.