True Random Numbers in SQL

Co-worker stumbled across this one and thought it was a good way to generate random numbers in SQL Server.

Often, random numbers are driven off of the uniqueness of the ticks/millisecond part of the current date/time but that code may run so quickly that you’re not guaranteed a different value between each random request. 

The solution – use GUIDs as the source for the random function as GUIDs are “guaranteed” to be unique.  Of course, some casting is involved as the random function does not take a string.  So, the solution is to cast the GUID as a var binary, then convert that to an int, then pass the absolute value of that that into rand (since it only allows positive ints).  Here’s the general code:

  1: RAND(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))

As normal, rand returns a float/double between 0 and 1.