Select n random rows from SQL Server table

How to Select Random Rows from SQL Server Table

So, you have a SQL Server table with a whopping 50,000 rows and you want to randomly select about 5,000 rows from it. 🤔 Don't worry, we've got you covered! We understand the pain of complex solutions and the struggle to find a simpler way to achieve your goal.

The Complicated Approach

Before we dive into an easier solution, let's take a look at the convoluted method mentioned in the question. It involves creating a temporary table, adding a "random number" column, and updating each row with the RAND() function. Then, selecting from the temporary table where the random number column is less than 0.1. 🌀 Phew, quite a bit of effort, right?

A Simpler Solution: Using the NEWID() Function

Fortunately, there's a simpler way to accomplish this in a single statement. 🎉

The solution lies in utilizing the NEWID() function. This function generates a uniqueidentifier (GUID) for each row in the table. By utilizing this function, we can choose random rows without any complicated configurations.

Here's an example query that demonstrates how to achieve this using the ORDER BY NEWID() clause:

SELECT TOP 5000 * FROM YourTable
ORDER BY NEWID()

This query will select the top 5000 rows from your table and order them randomly. 🎲

Selecting a Specific Percentage of Rows

Now, what if you want to select a certain percentage of rows, rather than a fixed number? 📊

To do this, you can utilize the TABLESAMPLE clause, but keep in mind that this method does not provide a truly random selection. It selects rows based on a statistical sampling algorithm. Here's an example query that selects 10% of the rows from your table:

SELECT * FROM YourTable TABLESAMPLE(10 PERCENT)

Remember, this method provides an approximation and may not give you an exact percentage of rows.

Wrap Up and Get Randomizing! 🎉

No, you don't need to go through all those complicated steps anymore! 😅 Using the NEWID() function and the TOP clause, you can easily select random rows from your SQL Server table. And if you want to select a specific percentage of rows, the TABLESAMPLE clause is your go-to option.

Now, it's your turn to dive into your SQL Server database and start experimenting with these techniques! Feel free to share your thoughts, experiences, or any other cool tips you have in the comments below. Let's keep the conversation flowing! 💬👇

🔗 Source