Select Random Rows in SQL Server

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

Method 1: Using NEWID()

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

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

This will return 10 random rows from the `Orders` table.

Method 2: Using TABLESAMPLE (SQL Server 2005 and later)

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

SELECT * FROM Orders TABLESAMPLE (10 ROWS);

This will return 10 random rows from the `Orders` table.

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 random rows.

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

This will return 10 random rows from the `Orders` table.

Example: Selecting 5 Random Rows from a Table

Let's say we have a table called `Products` with 100 rows, and we want to select 5 random rows.

SELECT TOP 5 * FROM Products ORDER BY NEWID();

This will return 5 random rows from the `Products` table.

Important Notes

When selecting random rows in SQL Server, keep in mind:

  • The `NEWID()` function can be used in most versions of SQL Server.
  • The `TABLESAMPLE` clause is only available in SQL Server 2005 and later versions.
  • 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.