从SQL中选择Random N Rows [英] Select Random N Rows from SQL

问题描述

我们需要一个内部网站，我们必须在一张巨大的桌子上显示10个随机帖子。我用谷歌搜索，发现这可以使用NewID实现。我不确定这是如何工作的，但有人可以解释一下查询对性能的影响吗？

We have a requirement for an internal site where we have to show 10 random posts from a huge table. I googled and found that this can be achieved using NewID. I am not exactly sure how this works, but can some one explain about the performance impact the query has?

推荐答案

这个页面很好地解决了你的顾虑和选择随机的方法行

我们如何以高效的方式从大表中选择N个随机行

我在下面总结一下，但是当我遗漏一些重要的怪癖时，请仔细阅读页面。



我认为你可以放心使用对于相当大的表没有太多性能影响的NEWID（）。

This page nicely addresses your concerns and ways to select random rows
How do we select N random rows from a large table in a performance efficient manner
I am summarizing below but do go through the page as I am leaving out some important quirks.

I think you can safely use NEWID() for fairly large tables without much performance impact.

SELECT TOP 10 FROM someTable ORDER BY NEWID()

但是如果你的桌子非常大且你的sql-服务器版本> = 2005，您可以使用TABLESAMPLE来限制您的工作集。

But if your table is extremely large and your sql-server version >= 2005, you can use TABLESAMPLE to restrict your working set.

SELECT TOP 10 * FROM someTable TABLESAMPLE (1000 ROWS) ORDER BY NEWID()

或许类似于：

SELECT ... FROM ... ORDER BY NEWID（）FETCH FIRST 10 ROWS仅





http://msdn.microsoft.com/en-us/library/ms188385.aspx [ ^ ]

Perhaps something like:
SELECT ... FROM ... ORDER BY NEWID() FETCH FIRST 10 ROWS ONLY


http://msdn.microsoft.com/en-us/library/ms188385.aspx[^]

关于在SQL中选择随机行的链接由r @ hu发布！在详细说明此类查询的性能改进方面做得非常出色。只是挑剔，但我想补充一点，你可以尝试使用校验和（NewId（））而不是NewID（）来看它是否能提高性能。

The link regarding selecting random rows in SQL posted by r@hu! does an excellent job in detailing performance improvement of such a query. Just nitpicking, but I'd like to add though that you may try using checksum(NewId()) instead of NewID() to see if it improves performance.

这篇关于从SQL中选择Random N Rows的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！