Generating Random numbers in a SELECT statement using MS SQL Server

Generating random numbers in SQL statement is not as trivial as it may look. Simply invoking the rand() method in a query will not work. For example, if you have a table with 1000 rows, doing:

select rand() from my_table

Will not work.

The result of this query will be 1000 rows of the same random number. MS SQL optimizer evaluates the rand() function only once.

In order to override this problem, we can do the following steps:

1) Create a view named: random_view that selects rand() function:

create VIEW [dbo].[random_view]
AS
SELECT rand() 'rnd'   

2) Create a function that makes a select on the random_view:

CREATE FUNCTION [dbo].[random]()
RETURNS float
AS
BEGIN
return (select rnd from random_view)
END

Now we can use our new generated function in a any query:

select dbo.random() from my_table

Assuming my_table has has 1000 rows, we will get 1000 different random numbers.