Thank you, Jeff. I went back to the Ben-Gan article and now realize that it addresses the behavior of RAND or NEWID when assigned in a table expression, such as a sub-query. In that regard, the two functions both now behave the same in providing a consistent result when referenced multiple times. This code gives you the same "rnd" three times and the same "nid" three times in SQL2005, but according to Ben-Gan, would give possibly different values for "rnd" and "nid" under SQL 2000.

select rnd, rnd, rnd, nid, nid, nid

from (select rand() as rnd

,abs(checksum(newid()))%100 + 1 as nid

) as d;

rnd rnd rnd nid nid nid

---------------------- ---------------------- ---------------------- ----------- ----------- -----------

0.455575000804869 0.455575000804869 0.455575000804869 54 54 54

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. That non-intuitive difference is probably reason enough to shove RAND to the back of the shelf and always use NEWID.

select abs(checksum(newid()))%10 + 1 as nid1

,abs(checksum(newid()))%10 + 1 as nid2

,abs(checksum(newid()))%10 + 1 as nid3

,abs(checksum(newid()))%10 + 1 as nid4

,cast(rand()*10 as int)%10 + 1 as rnd1

,cast(rand()*10 as int)%10 + 1 as rnd2

,cast(rand()*10 as int)%10 + 1 as rnd3

,cast(rand()*10 as int)%10 + 1 as rnd4

nid1 nid2 nid3 nid4 rnd1 rnd2 rnd3 rnd4

----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

8 10 6 4 2 3 10 7

DECLARE @MinValue INT , @MaxValue INT , @Quantity INT

SELECT @MinValue = 10, @MaxValue = 100, @Quantity = 10

SELECT TOP (@Quantity)

cast(rand()*(@MaxValue-@MinValue+1) as int)%(@MaxValue-@MinValue+1)+@MinValue as ConstRand

,ABS(CHECKSUM(NEWID()))%(@MaxValue-@MinValue+1)+@MinValue as NotConstNewID

FROM Master.sys.SysColumns sc1

ConstRand NotConstNewID

----------- -------------

88 64

88 59

88 76

88 11

88 41

88 38

88 26

88 63

88 89

88 53