john.arnott (3/2/2009)
So what's wrong with RAND(), you may be wondering? As Jeff notes, the RAND (and also the NEWID) functions are deliberately designed to be invoked a single time in a call, even if referenced multiple times. In SQL 2000, each reference produced a different result as the function would be evaluated again for each reference. This was a deliberate design decision at Microsoft.An interesting discussion on this topic was posted by Itzik Ben-Gan at http://www.sqlmag.com/Article/ArticleID/97032/sql_server_97032.html
Ah... but that's not true for NEWID() which will give you a different return for every row generated by a SELECT. The code I provided above is proof of that.
--Jeff Moden
Change is inevitable... Change for the better is not.