Select N Percent of Random Rows in SQL Server

In SQL Server, you can select a percentage of random rows from a table using various methods. This is useful when you want to retrieve a sample of data from a large table.

Method 1: Using TABLESAMPLE

The `TABLESAMPLE` clause can be used to select a percentage of random rows from a table.

SELECT * FROM Orders TABLESAMPLE (10 PERCENT);

This will return approximately 10% of the rows from the `Orders` table, randomly selected.

Method 2: Using NEWID() and TOP

The `NEWID()` function can be used to generate a random unique identifier for each row, and then use `TOP` to select a percentage of rows.

SELECT TOP 10 PERCENT * FROM Orders ORDER BY NEWID();

This will return approximately 10% of the rows from the `Orders` table, randomly selected.

Method 3: Using ROW_NUMBER() and NEWID()

The `ROW_NUMBER()` function can be used to assign a random row number to each row, and then select a percentage of rows.

WITH RandomRows AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum FROM Orders ) SELECT * FROM RandomRows WHERE RowNum <= (SELECT COUNT(*) * 0.1 FROM Orders);

This will return approximately 10% of the rows from the `Orders` table, randomly selected.

Example: Selecting 20% of Random Rows from a Table

Let's say we have a table called `Customers` with 1000 rows, and we want to select 20% of the rows randomly.

SELECT * FROM Customers TABLESAMPLE (20 PERCENT);

This will return approximately 200 rows from the `Customers` table, randomly selected.

Important Notes

When selecting a percentage of random rows in SQL Server, keep in mind:

  • The `TABLESAMPLE` clause is only available in SQL Server 2005 and later versions.
  • The `NEWID()` function can be used in most versions of SQL Server.
  • The `ROW_NUMBER()` function is only available in SQL Server 2005 and later versions.
  • Be careful when using these methods, as they can affect the performance of your queries.