How Can I Efficiently Select a Random Percentage of Rows in SQL Server?

Simplified Random Row Selection in SQL Server: A Streamlined Approach

SQL Server users frequently need to select a random sample of rows from a table. Finding a clean, efficient method for this has historically been problematic. This article presents a simplified solution.

Previous attempts often involved complex temporary tables and iterative processes. While the NEWID() function offers potential, reliably selecting a precise percentage proved difficult.

Here's a concise and efficient solution:

<code class="language-sql">SELECT TOP 10 PERCENT * FROM [yourtable] ORDER BY NEWID()</code>

This query uses ORDER BY NEWID() to randomly shuffle the rows. TOP 10 PERCENT then selects the top 10% of these shuffled rows.

For significantly large tables, a performance-optimized approach is recommended:

<code class="language-sql">SELECT * FROM [yourtable] WHERE [yourPk] IN (SELECT TOP 10 PERCENT [yourPk] FROM [yourtable] ORDER BY NEWID())</code>

This approach first selects a random sample of primary key values using a subquery. The main query then retrieves only the rows corresponding to these keys. This significantly reduces the data scanned, boosting performance.

These techniques offer a simple and effective way to perform random row selection in SQL Server, solving a previously complex problem.

The above is the detailed content of How Can I Efficiently Select a Random Percentage of Rows in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!