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.
- The returned values span the whole range of the result precision.
- All values are >= 0.
- But the small values are more numerous.
- These values are better suited for prices, for which most articles have small prices, and less have higher prices.
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
.