I am going to wrap up my short articles on non-trigonometric Transaction SQL math functions. Most, if not all, of these functions have been in the product before the release of SQL Server 2005.
I am very proud of my daughter making the advance math class in sixth grade. I am dedicating these blogs to her hard class work and love of the subject.
Today, I will be exploring the RAND() function. This function takes an optional seed number in integer format. It returns a pseudo-random float value from 0 through 1.
Many physical examples of random numbers have existed since ancient times including: rolling dice, coin flipping, playing cards, or yarrow sticks. It is not surprising that a random number function has been including in TSQL.
An optional seed can be passed to start off the random number generator. It is interesting to note, that the same seed will generate the same sequence of numbers. Therefore, one might want to use something like time HH:MM:SS NNN converted to a integer as a seed.
The examples below demonstrate both constant and variable seeding.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- -- RAND() - TSQL examples -- -- Return semi random numbers after constant seeding SELECT RAND(10) AS A, RAND() AS B, RAND() AS C, RAND() AS D, RAND(NULL) AS UNKNOWN; GO -- Return random numbers after random seeding DECLARE @TIME INT = REPLACE(CONVERT(CHAR, GETDATE(), 114), ':', ''); SELECT RAND(@TIME) AS A, RAND() AS B, RAND() AS C, RAND() AS D, RAND(NULL) AS UNKNOWN; GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 |
A B C D UNKNOWN ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 0.713759689954247 0.182458908613686 0.586642279446948 0.852383912025182 NULL 213331990 A B C D UNKNOWN ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 0.722460475884181 0.335711397999013 0.831828119554167 0.71871088122395 NULL |
If you need to generate a random data set in the future, do not forget about the RAND() function. I have used it in the past to pick common first and last names to come up with fictitious people. Take a look at my data warehousing talk that has code that generates data using this exact algorithm.
In summary, talking about non-trigonometric math functions was fun. I will be back to this topic another time to review trigonometric functions. I just have to find my triangles first!
