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

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

Newid ()/order by可以工作，但对于大型结果集来说代价非常高，因为它必须为每一行生成一个id，然后对它们进行排序。

从性能的角度来看，TABLESAMPLE()很好，但是您将得到结果的聚集(将返回页面上的所有行)。

为了获得更好的真实随机样本，最好的方法是随机过滤掉行。我在SQL Server Books Online文章使用TABLESAMPLE限制结果集中找到了以下代码示例:

If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE. For example, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table: SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) The SalesOrderID column is included in the CHECKSUM expression so that NEWID() evaluates once per row to achieve sampling on a per-row basis. The expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 1.

当对一个有1,000,000行的表运行时，下面是我的结果:

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

如果您可以使用TABLESAMPLE，它将为您提供最佳性能。否则使用newwid ()/filter方法。如果结果集很大，Newid ()/order by应该是最后的选择。

从MSDN上的大表中随机选择行有一个简单的、明确的解决方案，可以解决大规模性能问题。

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

试试这个:

SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()

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

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

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

 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条件