|
|
Posted by Hugo Kornelis on 06/13/07 23:29
On Mon, 11 Jun 2007 01:02:19 +0200, Hugo Kornelis wrote:
> I know that rand() is called
>just once for a set-based query, returning the same value for each row.
Which, BTW, can be overcome in SQL Server 2005 using a dirty trick:
SELECT o.name, r.rnd
FROM sys.objects AS o
CROSS APPLY (SELECT RAND(CHECKSUM(o.name) ^ CHECKSUM(newid())) AS rnd)
AS r
The CHECKSUM(o.name) makes sure that the RAND function has to be called
for each row in sys.objects. With just this, the query would become
deterministic; this is overcome by also factoring in CHECKSUM(NEWID()).
Both CHECKSUM values can span the entire integer range; combining them
with bitwise exclusive OR results in a new integer that also spans the
entire range of integers. (Bitwise inclusive OR favors values with many
bits said; bitwise AND favors values with many bits off; adding or
subtracting runs the risk of exceeding the integer domain; and
subtracting the absolute values favors values around 0).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|