Populating a Column with Random Values

Suppose you have a table:

create table t(i int, d float)
insert into t values(1, 0.0)
insert into t values(2, 0.0)
insert into t values(3, 0.0)
insert into t values(4, 0.0)

Suppose you want to populate d with random values:

update t set d = rand()

Unfortunately it will assign one and the same value to all the rows:

select * from t

i d----------- ----------------------
1 0.430774201738828
2 0.430774201738828
3 0.430774201738828
4 0.430774201738828
(4 row(s) affected)


Here is the right way to do it:

create view wrapped_rand_view
as
select rand( ) as random_value
go
create function wrapped_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from wrapped_rand_view)
return @f
end
go
update t set d = dbo.wrapped_rand()

select * from t

i d
----------- ----------------------
1 0.665934939858962
2 0.99919527046644
3 0.844039650356367
4 0.146401486624627
(4 row(s) affected)