Random sequence in Transact SQL query
The rand()
Transact SQL function produces a sequence of same numbers in the query. For example:
SELECT rand() AS rand_value, id FROM ( SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 3 AS id ) AS t1;
This query shows the following result:
rand_value id ---------------------- ----------- 0,581556027773794 1 0,581556027773794 2 0,581556027773794 3
In addition, you cannot use the rand()
function in any user defined function (UDF) wrapper because of the SQL Server UDF limitations.
CREATE OR ALTER FUNCTION dbo.my_rand() RETURNS float AS BEGIN RETURN rand(); END
Msg 443, Level 16, State 1, Procedure my_rand, Line 4 [Batch Start Line 0] Invalid use of a side-effecting operator 'rand' within a function.
Sure, you can develop your own user defined function based on some algorithm generating pseudo-random sequences. However, let's try to not invent a bicycle before.
An integer number (seed) may be passed into rand()
and the function will return a random sequence in the (0, 1) interval. Where could we take the seed? Fortunately, the GUID generated by newid()
function may be converted into this kind of number. For example:
rand(abs(convert(int, convert(varbinary, newid()))))
As you cannot use UDF let's create a view to encapsulate the generator.
CREATE VIEW rand2 AS SELECT rand(abs(convert(int, convert(varbinary, newid())))) AS rand_value;
Now you are able to to use this view in queries:
SELECT rand2.rand_value, some_table.* FROM some_table CROSS JOIN rand2
or to get a single value
SELECT rand_value FROM rand2
Go back to the initial example and voila!
SELECT rand2.rand_value, id FROM ( SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 3 AS id ) AS t1 CROSS JOIN rand2;
rand_value id ---------------------- ----------- 0,349013124792225 1 0,780734712117597 2 0,488939877887713 3
However, the question on the length of non-repetitive sequence stays open. I'd like to leave you're thinking about it later.