SQL Server 2017如何实现真正随机选取10条表数据？

Let's tackle your problem directly—you're right that some "random" methods can lead to skewed distributions, even if they look correct on paper. Let's break down why your test was showing poor randomness and fix it with reliable approaches.

Why Your Previous Methods Failed #

First, let's unpack the approach you tested:

SELECT top 10 ID FROM @TestData order by abs(checksum(newid())) % 100

While CHECKSUM(NEWID()) generates a random integer, taking % 100 creates a subtle but real bias. The full range of CHECKSUM(NEWID()) spans the entire int spectrum (-2,147,483,648 to 2,147,483,647). When you take the absolute value and mod by 100, values near the edges of the int range don't map evenly to 0-99. This leads to some random numbers being slightly more likely, which in turn skews which rows get picked.

The commented-out ORDER BY NEWID() is actually the standard go-to method, but maybe you didn't give it a proper test run? Let's validate that first.

The Most Reliable Method for Small-to-Medium Tables #

For tables like your test set (801 rows), ORDER BY NEWID() is simple, effective, and produces a uniform random distribution. Here's how to adapt it to your test case:

DECLARE @Random TABLE ( Id int, [Count] int )
DECLARE @TestData TABLE ( Id int )
declare @runs int = 0;

-- Populate test data
WHILE (@runs <=800) begin
insert into @TestData values(@runs)
set @runs = @runs +1
end;

-- Run the test with ORDER BY NEWID()
set @runs = 0
WHILE (@runs <=100) begin
MERGE @Random AS target
USING (SELECT top 10 ID FROM @TestData order by NEWID()) AS SOURCE
ON (target.id = source.id)
WHEN MATCHED THEN UPDATE SET Target.[Count] = Target.[Count] + 1
WHEN NOT MATCHED THEN INSERT (ID, [Count]) VALUES (source.ID, 1);
set @runs = @runs +1
end

-- Check distribution
select [count], count(*) "count(*)" from @Random group by [count] order by 1 desc

When you run this, you'll see the counts cluster around 1-2 (since 100 runs ×10 rows = 1000 total selections, divided by 801 IDs ≈1.25 per ID)—exactly the uniform distribution you'd expect from true random sampling.

For Larger Tables: Performance-Optimized Random Selection #

If you're working with tables that have millions of rows, ORDER BY NEWID() can be slow because it generates a GUID for every row and sorts them all. In that case, use a combination of ROW_NUMBER() and a random seed to avoid full-table sorting:

WITH RandomizedRows AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS RandomRowNum
    FROM YourLargeTable
)
SELECT TOP 10 ID
FROM RandomizedRows
WHERE RandomRowNum <= 10

This still uses NEWID() for randomness but avoids sorting the entire table—the window function handles this more efficiently in most cases.

Encryption-Grade Randomness (For Extra Assurance) #

If you need even stronger randomness (e.g., for security or statistical analysis), use CRYPT_GEN_RANDOM()—SQL Server's encryption-level random number generator. It produces a more uniform distribution than NEWID() in edge cases:

SELECT TOP 10 ID
FROM @TestData
ORDER BY CONVERT(int, CRYPT_GEN_RANDOM(4))

CRYPT_GEN_RANDOM(4) generates 4 random bytes, which we convert to an int to use as a sort key. This is overkill for most everyday use cases, but it's perfect when you need maximum randomness.

Key Takeaways #

• For most scenarios, SELECT TOP N ... ORDER BY NEWID() is the best balance of simplicity and randomness.
• Avoid using % (modulo) with checksummed GUIDs—it introduces avoidable bias.
• For large tables, use ROW_NUMBER() with NEWID() to optimize performance.
• For encryption-grade randomness, switch to CRYPT_GEN_RANDOM().

内容的提问来源于stack exchange，提问作者Jens Borrisholt