john.arnott (3/3/2009)
In that regard, the two functions both now behave the same in providing a consistent result when referenced multiple times.
Of course it will... they've used the same GUID 3 times for the same row... the following should make it crystal clear for any who are still in doubt...
SELECT GETDATE() AS [GETDATE() (Always Same)],
RAND() AS [RAND() (Always Same)],
NEWID() AS [NEWID() (NEVER Same)]
FROM Master..spt_Values
WHERE Type = 'P'
AND Number < 10
The article did not mention the difference between RAND and NEWID that you've pointed out. By comparison, here are two queries with sample output. In the first, both RAND and NEWID return new values for each reference. In the second, they behave differently, a distinction not made clear in BOL's "Behavior Change" table or in Ben-Gan's article. [font="Arial Black"]That non-intuitive difference is probably reason enough to shove RAND to the back of the shelf and always use NEWID.[/font]
Heh... once converted to VARBINARY or some such, you can even used NEWID() as a seed for RAND if it's more intuitive. But, beware... Peter Larson, Matt Miller, and Michael Valentine Jones, I have all done some pretty good testing on it... the methods of using NEWID() with CHECKSUM are quite a bit faster than the VARBINARY conversion and RAND().
I agree... knowing it's extreme limitations, I don't even bother with RAND() except in the rarest of cases.
--Jeff Moden
Change is inevitable... Change for the better is not.