What is SQL Server Rand Function algorithm?

Question

1. I want to know the exact algorithm of SQL Server 2005 RAND function
2. Is there any time factor involved in the logic?

I have searched in the web and couldn't find any details. Some people believe that the RAND function is not fully random and by having enough sample they can predict the next one.

Answer 1

The RAND() generates numbers based on the seed parameter.

Syntax: RAND ( [ seed ] )

Per MSDN,

• If seed is not specified, the SQL Server Database Engine assigns a seed value at random.
• For a specified seed value, the result returned is always the same.
• For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call.

For example, if you run this code in multiple connections (even on multiple databases or database servers at different times)

SELECT RAND(100), RAND(), RAND() 

you will get same results, below.

(No column name)    (No column name)    (No column name)
0.715436657367485   0.28463380767982    0.0131039082850364

So if some one can predict the seed value you used, they may be able to guess resulting random numbers.

That should confirm that RAND() definitely does not use time or mac address kind of inputs into the algorithm.

This page at sql-server-helper.com suggests using NEWID() as the better way of generating unique random numbers. But NEWID() is available on SQL 2008 or later.

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

Disclaimer: I am not associated with www.sql-server-helper.com.