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.