Random for each line

Normally RAND() in TSQL returns the same number for the whole query to save time. But when you are generating test data you want different numbers for each line.

This is a utility function that does that expanded to provide int values between min and max with min and max values included

First trick is to create a view so that the user defined function does not fail because of sideeffects

CREATE VIEW ViewRandom
AS
SELECT RAND() AS Random
GO

The function itself

CREATE FUNCTION RandomNumber
(
@Min INT
, @Max INT
)
RETURNS INT
AS
BEGIN
RETURN FLOOR((SELECT Random FROM ViewRandom) * (@Max - @Min) + @Min -1)
END
GO

It is used this way:

SELECT dbo.RandomNumber(2,10) AS Random

Leave a Reply

Your email address will not be published. Required fields are marked *