How Can I Efficiently Select Random Rows from a SQL Server Table?

A Streamlined Approach to Random Row Selection in SQL Server

Extracting a random subset of rows from a sizable SQL Server table can be computationally intensive. Rather than employing intricate temporary tables and iterative processes, a more efficient and straightforward method utilizes SQL Server's inherent functions.

The NEWID() function generates a unique random GUID, providing a foundation for random row selection. To retrieve a 10% random sample from a table named [yourtable], use this concise query:

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

For significantly larger tables, performance optimization is achieved with this technique:

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

This approach leverages a key scan to pinpoint primary key values for the selected rows, subsequently joining with the original table to retrieve the complete row data. This join operation generally incurs minimal overhead, particularly for small percentage selections on large datasets.

These streamlined techniques offer developers a highly efficient means of selecting random samples from SQL Server tables, avoiding complex and resource-demanding alternatives.

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