这是为每条记录生成随机数的好方法还是坏方法? [英] Is this a good or bad way of generating random numbers for each record?

问题描述

我的一位同事在 SQL Server 中发现了一个我不知道的行为.

A colleague of mine discovered a behaviour in SQL Server which I was unaware of.

CREATE VIEW dbo.vRandNumber AS
SELECT RAND() as RandNumber
GO

CREATE FUNCTION dbo.RandNumber() RETURNS float AS
RETURN (SELECT RandNumber FROM vRandNumber)
GO

DECLARE @mytable TABLE (id INT)
INSERT INTO @mytable SELECT 1
INSERT INTO @mytable SELECT 2
INSERT INTO @mytable SELECT 3

SELECT *, dbo.RandNumber() FROM @mytable

这似乎是为数据集中的每条记录生成随机"值的最快方法.但我不完全确定这是记录行为的结果，还是利用了奇怪的巧合.

This seems to be the quickest way of generating a 'random' value for each record in a data set. But I'm not completely sure if it's a result of documented behaviour, or taking advantage of a bizarre convergance of coincidences.

你会使用这样的东西吗?

编辑

这不是关于 RAND() 函数本身的优点的问题，而是关于使用 UDF/VIEW 组合来强制它对每一行重新计算的问题.(在最终查询中仅使用 RAND() 而不是 dbo.RandNumber() 将为每条记录提供相同的值.)

This isn't a question about the merits of the RAND() function itself, but the use of the UDF/VIEW combination to force it to recalculate on every row. (Using just RAND() in the final query, instead of dbo.RandNumber(), would give the same value for every record.)

此外，重点是每次查看时的值都不同.例如，启用随机选择记录.

Also, the point is for the value to be different every time you look at it. So enabling random selection of records, for example.

编辑

对于 SQL Server 2000+.

For SQL Server 2000+.

推荐答案

对于我想继续开发 SQL Server 未来版本的软件，我不会这样做.我找到了一种方法，可以为 select 语句中的每一行从 RAND() 返回不同的值.这个发现是 1) 有点小技巧和 2) 是在 SQL Server 2005 上进行的.它不再适用于 SQL Server 2008.这种经历让我更加担心依靠诡计来让 rand() 返回每个随机值行.

I would not do this for a piece of software I wanted to continue working on future versions of SQL Server. I found a way to return a different values from RAND() for each row in a select statement. This discovery was 1) a bit of a hack and 2) was made on SQL Server 2005. It no longer works on SQL Server 2008. That experience makes me extra leary of relying on trickery to get rand() to return a random value per row.

此外，我相信允许 SQL Server 优化掉对 UDF 的多次调用......尽管这可能会改变，因为它们现在确实允许一些非确定性函数.

Also, I believe SQL Server is allowed to optimize away the multiple calls to a UDF ... though that might be changing since they do allow some non-deterministic functions now.

仅适用于 SQL Server 2005，一种强制 rand() 在 select 语句中每行执行的方法.不适用于 SQL Server 2008. 未在 2005 之前的任何版本上进行测试:

For SQL Server 2005 only, a way to force rand() to execute per row in a select statement. Does not work on SQL Server 2008. Not tested on any version prior to 2005:

create table #t (i int)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)

select i, case when i = 1 then rand() else rand() end as r
from #t

1   0.84923391682467
2   0.0482397143838935
3   0.939738172108974

另外，我知道你说你不是在问 rand() 的随机性，但我会很好地参考:http://msdn.microsoft.com/en-us/library/aa175776(SQL.80).aspx.它将 rand() 与 newid() 和 rand(FunctionOf(PK, current datetime)) 进行比较.

Also, I know you said you were not asking about the randomness of rand(), but I will a good reference is: http://msdn.microsoft.com/en-us/library/aa175776(SQL.80).aspx. It compares rand() to newid() and rand(FunctionOf(PK, current datetime)).

这篇关于这是为每条记录生成随机数的好方法还是坏方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！