这个链接在Orderby(NEWID())和其他方法之间进行了有趣的比较，这些方法用于具有1,700万行和1,300万行的表。

通常，当讨论组中询问如何选择随机行时，会提出NEWID查询;它很简单，适用于小表。

SELECT TOP 10 PERCENT *
  FROM Table1
  ORDER BY NEWID()

但是，当您将newd查询用于大型表时，它有一个很大的缺点。ORDER BY子句会将表中的所有行复制到tempdb数据库中，并对它们进行排序。这会导致两个问题:

排序操作通常有很高的成本。 排序会使用大量磁盘I/O，并且会运行很长时间。 在最坏的情况下，tempdb可能会耗尽空间。在 在最好的情况下，tempdb会占用大量的磁盘空间 如果没有手动收缩命令，将永远不会回收。

您需要的是一种不使用tempdb且不会随着表变大而变慢的随机选择行的方法。这里有一个关于如何做到这一点的新想法:

SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

这个查询背后的基本思想是，我们希望为表中的每一行生成一个0到99之间的随机数，然后选择那些随机数小于指定百分比值的所有行。在这个例子中，我们希望随机选择大约10%的行;因此，我们选择随机数小于10的所有行。

请在MSDN上阅读全文。

我在子查询中使用它，它在子查询中返回我相同的行

 SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

然后我解决了包括父表变量在哪里

SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              Where Mytable.ID>0
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

注意where条件

select top 10 percent * from [yourtable] order by newid()

针对关于大型表的“纯粹垃圾”评论:您可以这样做以提高性能。

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

这样做的成本将是值的关键扫描加上连接成本，对于一个选择百分比较小的大表来说，这应该是合理的。

服务器端使用的处理语言(如PHP， .net等)没有指定，但如果是PHP，获取所需的数字(或所有记录)，而不是在查询中随机使用PHP的shuffle函数。我不知道。net是否有等价的函数但如果有的话，请使用。net

ORDER BY RAND()可能会有相当大的性能损失，这取决于涉及多少记录。

这是最初的种子思想和校验和的组合，在我看来，它可以给出适当的随机结果，而不需要NEWID()的代价:

SELECT TOP [number] 
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())

这是一个更新和改进的抽样形式。它基于与其他一些使用CHECKSUM / BINARY_CHECKSUM和modulus的答案相同的概念。

使用与此类似的实现的原因，而不是其他答案:

It is relatively fast over huge data sets and can be efficiently used in/with derived queries. Millions of pre-filtered rows can be sampled in seconds with no tempdb usage and, if aligned with the rest of the query, the overhead is often minimal. Does not suffer from CHECKSUM(*) / BINARY_CHECKSUM(*) issues with runs of data. When using the CHECKSUM(*) approach, the rows can be selected in "chunks" and not "random" at all! This is because CHECKSUM prefers speed over distribution. Results in a stable/repeatable row selection and can be trivially changed to produce different rows on subsequent query executions. Approaches that use NEWID(), such as CHECKSUM(NEWID()) % 100, can never be stable/repeatable. Allows for increased sample precision and reduces introduced statistical errors. The sampling precision can also be tweaked. CHECKSUM only returns an int value. Does not use ORDER BY NEWID(), as ordering can become a significant bottleneck with large input sets. Avoiding the sorting also reduces memory and tempdb usage. Does not use TABLESAMPLE and thus works with a WHERE pre-filter.

缺点/限制:

Slightly slower execution times and using CHECKSUM(*). Using hashbytes, as shown below, adds about 3/4 of a second of overhead per million lines. This is with my data, on my database instance: YMMV. This overhead can be eliminated if using a persisted computed column of the resulting 'well distributed' bigint value from HASHBYTES. Unlike the basic SELECT TOP n .. ORDER BY NEWID(), this is not guaranteed to return "exactly N" rows. Instead, it returns a percentage row rows where such a value is pre-determined. For very small sample sizes this could result in 0 rows selected. This limitation is shared with the CHECKSUM(*) approaches.

要点如下:

-- Allow a sampling precision [0, 100.0000].
declare @sample_percent decimal(7, 4) = 12.3456

select
    t.*
from t
where 1=1
    and t.Name = 'Mr. No Questionable Checksum Usages'
    and ( -- sample
        @sample_percent = 100
        or abs(
            -- Choose appropriate identity column(s) for hashbytes input.
            -- For demonstration it is assumed to be a UNIQUEIDENTIFIER rowguid column.
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )

注:

While SHA1 is technically deprecated since SQL Server 2016, it is both sufficient for the task and is slightly faster than either MD5 or SHA2_256. Use a different hashing function as relevant. If the table already contains a hashed column (with a good distribution), that could potentially be used as well. Conversion of bigint is critical as it allows 2^63 bits of 'random space' to which to apply the modulus operator; this is much more than the 2^31 range from the CHECKSUM result. This reduces the modulus error at the limit, especially as the precision is increased. The sampling precision can be changed as long as the modulus operand and sample percent are multiplied appropriately. In this case, that is 1000 * to account for the 4 digits of precision allowed in @sample_percent. Can multiply the bigint value by RAND() to return a different row sample each run. This effectively changes the permutation of the fixed hash values. If @sample_percent is 100 the query planner can eliminate the slower calculation code entirely. Remember 'parameter sniffing' rules. This allows the code to be left in the query regardless of enabling sampling.

计算@sample_percent，带下限/上限，并在查询中添加TOP“提示”，这在示例用于派生表上下文时可能有用。

-- Approximate max-sample and min-sample ranges.
-- The minimum sample percent should be non-zero within the precision.
declare @max_sample_size int = 3333333
declare @min_sample_percent decimal(7,4) = 0.3333
declare @sample_percent decimal(7,4) -- [0, 100.0000]
declare @sample_size int

-- Get initial count for determining sample percentages.
-- Remember to match the filter conditions with the usage site!
declare @rows int
select @rows = count(1)
    from t
    where 1=1
        and t.Name = 'Mr. No Questionable Checksum Usages'

-- Calculate sample percent and back-calculate actual sample size.
if @rows <= @max_sample_size begin
    set @sample_percent = 100
end else begin
    set @sample_percent = convert(float, 100) * @max_sample_size / @rows
    if @sample_percent < @min_sample_percent
        set @sample_percent = @min_sample_percent
end
set @sample_size = ceiling(@rows * @sample_percent / 100)

select *
from ..
join (
    -- Not a precise value: if limiting exactly at, can introduce more bias.
    -- Using 'option optimize for' avoids this while requiring dynamic SQL.
    select top (@sample_size + convert(int, @sample_percent + 5))
    from t
    where 1=1
        and t.Name = 'Mr. No Questionable Checksum Usages'
        and ( -- sample
            @sample_percent = 100
            or abs(
                convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
            ) % (1000 * 100) < (1000 * @sample_percent)
        )
) sampled
on ..