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.