I was answering a question hereabouts and fired up my SSMS to test a little query before posting it, but got some strange results. Here's the query:
UPDATE Person
SET Pos_X = Rand()
, Pos_Y = Rand(id)
SELECT ID, Surname, Forename, Pos_X, Pos_Y FROM Person
And here are is the result set:
1 Bloggs Fred 0.332720913214171 0.713591993212924
2 Doe Jane 0.332720913214171 0.713610626184182
3 Smith Mary 0.332720913214171 0.71362925915544
4 Jones Martha 0.332720913214171 0.713647892126698
5 Jones Martha 0.332720913214171 0.713666525097956
6 Jones Martha 0.332720913214171 0.713685158069215
7 Jones Martha 0.332720913214171 0.713703791040473
8 Jones Martha 0.332720913214171 0.713722424011731
9 Jones Martha 0.332720913214171 0.713741056982989
As I expected Rand without a seed put the same result in each row, but I was hoping that rand with a seed (albiet just the numbers 1 to 9) would do a bit better than an ordered list within 0.0002 of each other!
Do you get the same? This sounds like something that could catch out the unwary.
As I'm sure this will be relevant:
@@Version = 'Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1) '
RAND (Transact SQL):
Returns a pseudo-random float value from 0 through 1, exclusive.
And:
Repetitive calls of RAND() with the same seed value return the same results.
(emphasis mine)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With