drogers (6/9/2009)
Jeff Moden (6/9/2009)
RAND() in T-SQL works exactly the same way as RAND() in any computer language.... one row or value at a time.
No, it doesn't work at all like most languages. Most people expect RAND() in the result set to generate multiple values when the result set contains multiple rows. It doesn't. In my experience, this functionality is pretty unique.
A minor quibble with the article: the problem is not that RAND() is called multiple times with the same seed, once per row. The problem is that RAND() is only called once per query (just like GETDATE()). Adding the NEWID() call is a hack that forces the query optimizer to call RAND() once per row.
Great article!
David
Stop and think about it... in every other language, you only have control over one row at a time. In declarative languages like T-SQL, it's a set. If you use RAND() in a cursor like you would in C# or any other language, you are affecting only one row at a time.
That's not to say that I agree with the idea that RAND() doesn't work like NEWID() in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.