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

Efficiently extract random rows from SQL Server tables

Generating random samples from SQL Server tables is a useful operation for a variety of purposes. A common solution is to randomly select a fixed number of rows from the table.

Disadvantages of complex methods

The issues mentioned in the article point out complex methods using temporary tables and RAND() loops. These methods are inefficient and inflexible.

Limitations of the NEWID() method

The article also mentions an alternative method using the NEWID() function. However, this approach may not be suitable for the requirement of selecting a specific percentage of rows.

T-SQL solution

In order to solve this problem, the article provides a SQL Server script:

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

This statement uses the NEWID() function and the TOP clause to select the top 10% of rows and return a random sample of the table.

Optimization methods for large tables

For large tables, performance can be improved by using the following modified script:

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

This method works in two steps: first, select random primary key values; then, use these primary key values ​​to retrieve the corresponding rows. It balances key scan and join costs, making it more efficient for large tables.

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