RANDOM_NUMERIC (TSQL Function)

Returns a random numeric value, which precision and scale are specified by the arguments.

Syntax

RANDOM_NUMERIC ( p , s )

Arguments

p
precision of the result, of type int. It must be a constant literal.
s
scale of the result, of type int. It must be a constant literal.

Return Types

Returns numeric(p, s).

Remarks

The returned value is not uniformly distributed.

This function doesn’t exist in MS SQL Server.

You should use it to easily fill your test tables with random numeric.

Examples

CREATE TABLE articles (
        id          INT           NOT NULL IDENTITY(100, 1) PRIMARY KEY,
        description VARCHAR(50)   NOT NULL,
        quantity    INT           NOT NULL,
        price       NUMERIC(12,2) NULL
    );

    GO

    DECLARE @i INT = 0

    SET NOCOUNT ON
    BEGIN TRAN

    WHILE @i < 100 -- insert 100 records
        BEGIN

        -- we want the prices of the articles to be < 4000.00

        INSERT INTO articles (description, quantity, price)
        VALUES (
          UPPER(RANDOM_VARCHAR(5,10)),
          RANDOM_INT(0,1000),
          RANDOM_NUMERIC(6,2) * 0.4 -- random_numeric returns a number between 0000.00 and 9999.99
        )                           --   Multiply by 0.4 to have a number < 4000.00

        SET @i += 1
        IF @i % 10000 = 0
            PRINT 'records inserted: ' + CAST(@i as VARCHAR(100))

        END

    COMMIT
    SET NOCOUNT OFF


    PRINT ''
    PRINT 'print 10 sample records:'
    PRINT ''
    SELECT TOP(10) * FROM articles

The result is:

id         |description                   |quantity   |price      |
-----------+------------------------------+-----------+-----------+
        100|AZAGIGOW                      |        916|     173.68|
        101|XSOBAGOX                      |        639|     277.59|
        102|KAFUCGAAFE                    |        128|    3898.97|
        103|UQHUPIP                       |        511|      20.31|
        104|IGOTIHA                       |        753|       0.78|
        105|EZUXE                         |        119|    1348.88|
        106|UCOMEEJAV                     |        880|    2624.46|
        107|MUNOTOIHU                     |        517|    2466.33|
        108|NUFUGASASO                    |        567|    1111.24|
        109|OZANAJ                        |        355|     356.74|

(10 row(s) affected)

See also RANDOM_VARCHAR.

RSQL, a simple alternative to Microsoft SQL Server