Random Insert/Select with T-SQL

Here’s something I found pretty useful. The situation is I need to insert entries into the database with one of the columns being some random data. If I did a Rand() directly in the statement:

Insert into NewStudy
select studyid,((29 + 1) - 27) * Rand() + 27,null from StudyTable

It will only generate Rand() once and fill in all rows with the same data, which is not what I want.

In order to generate a random number for each row, I have to create a view of the RAND() function:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber
go
 
Insert into NewStudy
select studyid,((29 + 1) - 27) * (select RandNumber from vRandNumber) + 27,null 
from StudyTable

This works very well. Of course, 29 and 27 are my min and max respectively. I can create a separate function to do the same thing:

CREATE FUNCTION RandNumber(@Min int, @Max int)
RETURNS float
AS
 BEGIN
 RETURN @Min + (select RandNumber from vRandNumber) * ((@Max+1)-@Min)
 END

So instead, I can do RandNumber(27, 29) within the statement.

The problem why I didn’t use this randomization is because of permissions. Elevating a standard user to allow creation of a view is disallowed in my situation.

So this is what I did instead, to keep within the boundaries of the permissions, is to use row_number() instead. Take the mod of row_number by ((@Max + 1) - @Min) which is 3 for my case and add the @Min to it. It’s not random, but deterministic, but it works well enough for my case.

Insert into NewStudy
select studyid, (row_number() over (order by studyid)) % 3 + 27,null
from StudyTable

So that’s it. A deterministic pseudo “random” (not so random it seems) number based on row_number(). I wonder if there are other solutions to this.