I'm not sure for SQL Server 2008, but earlier versions of SQL-server would return the same value for rand() for all records within one select-statement. Other types of database, recalculate rand() for every record, as one would expect.
CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James
| [reply]
[d/l]
[select] |
I wasn't aware of that behaviour, and it seems to be undocumented. At least, in the SQL Server 2000 documentation of the rand() function, there is no trace of a document describing this behaviour. Unfortunately, I currently don't have access to any SQL Server installation.
If the SQL Server really behaves that brainf*ed (and I have no problem to believe that), using rand() would not help. But I don't think that newid() gives sufficiently random output. It seems to return a UUID, which can be constructed from a machine-specific constant and the local time, at least in v1 and v2. v3 and v5 are hash functions, only v4 is really random. Again, I could not find a document stating how newid() is implemented.
Alexander
--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
| [reply] |