[SQL Server] random and approximate Functions

Newid ()

Randomly retrieve 10 records from Table A using

Select top 10 * From northwind. DBO. Orders

Order by newid ();

Or

Select top 10 *, newid () as random from northwind. DBO. Orders

Order by random;

In SSMs, we can see that their execution plans are the same.

 

Example:

A. Use the newid function for variables

DECLARE @myid uniqueidentifierSET @myid = NEWID()PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

Result:

Value of @ myid is: 6f9619ff-8b86-d011-b42d-00c04fc964ff

B. Use newid In the create table statement

CREATE TABLE cust(CustomerID uniqueidentifier NOT NULLDEFAULT newid(),Company varchar(30) NOT NULL,ContactName varchar(60) NOT NULL, [Address] varchar(30) NOT NULL)GOINSERT cust(CustomerID, Company, ContactName, [ADDRESS])VALUES(NEWID(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38')

Rand ()

Returns a pseudo-random value between 0 and 1 (excluding 0 and 1 ).FloatValue.

Syntax

    RAND ( [ seed ] )  

Parameters
    Seed:Provide an integer expression for the seed value (Tinyint,SmallintOrInt). If not specifiedSeedThe Database Engine randomly allocates the seed value. For the specified seed value, the returned results are always the same.

Example:

Select rand (), returns a random decimal number similar to 0.36361513486289558

Ceiling ()

Returns the smallest integer greater than or equal to the specified value expression.

Syntax:

   CEILING ( numeric_expression )

Parameters
Numeric_expressionIs a type of exact or approximate numeric data (BitExcept for the data type.

Example:Displays the positive, negative, and zero values of the ceiling function.

     SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)

Result:

--------- --------- ------------------------- 124.00    -123.00    0.00                     (1 row(s) affected)

Floor ()

Returns the maximum integer that is less than or equal to the specified value expression.

Syntax

    FLOOR ( numeric_expression )

Parameters
Numeric_expressionIs a type of exact or approximate numeric data (BitExcept for the data type.

Example:Display positive, negative, and currency valuesFLOORFunctions.

    FLOOR ( numeric_expression )

Result:

    SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)

    ---------      ---------     -----------    123            -124          123.0000

How to generate a random integer?

1.

A: Select floor (RAND () * n) --- 12.0

B: Select cast (floor (RAND () * n) as INT) --- 12

2.

A: Select ceiling (RAND () * n) --- 12.0

B: Select cast (ceiling (RAND () * n) as INT) --- 12

Number range of Method 1: 0 to N-1, for example cast (floor (RAND () * 100) as INT) will generate any integer between 0 and 99

The number range of Method 2: 1 to n. For example, cast (ceiling (RAND () * 100) as INT) generates any integer between 1 and 100.