Select random elements - SQL Server

Question

I need to select 10 random elements from a table.I know how to do that, else this question have been answered a million times here on SO. By my problem is the randomisation isn't good enough

I've sat up the test case showing my problem :

DECLARE @Random TABLE
(
  Id int, 
  [Count] int  
)

DECLARE @TestData TABLE
(
  Id int
)

declare @runs int = 0;

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

 set @runs = 0 

 WHILE (@runs <=100)
 begin
     MERGE @Random AS target  
--      USING (SELECT ID FROM @TestData  where 0.01 >= CAST(CHECKSUM(NEWID(), id) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) ) 
--      USING (SELECT top 10 ID FROM @TestData order by newid()) 
        USING (SELECT top 10 ID FROM @TestData order by abs(checksum(newid())) % 100)

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

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

As you can see I've tried several methods in order of randomisation. But every time I end up with a result like this :

So in short how do I select really random elements from a table?

Scope: SQL Server 2017, so every language feature is acceptable

Is importing a C# DLL with a custom random function in scope? Technically it's not a *language feature* — user6144226, Jan 09 '18 at 11:57
i don't really understand what you're trying to show with the output? — Tanner, Jan 09 '18 at 12:02
Think the issue is with your output query, use this: ` select id, count(*) AS 'count(*)' from @Random group by id order by 1 desc ` and you might see your error. Remember don't use Keywords as column names. — Tanner, Jan 09 '18 at 12:07
This is just at small test example. Column names are out of scope — Jens Borrisholt, Jan 09 '18 at 12:12
@podiluska because when I run this 100 times I get the same ID 6 times and only approx 600 IDs out of 800 have been selected. So the question is: Can i be done any better? — Jens Borrisholt, Jan 09 '18 at 12:14
@JensBorrisholt So what you're saying is some IDs get selected multiple times, and some not at all? That is randomness. — podiluska, Jan 09 '18 at 12:24
@podiluska yes i know, but more like could it be done any better? — Jens Borrisholt, Jan 09 '18 at 12:28
You might read this: https://stackoverflow.com/q/31468836/5089204 — Shnugo, Jan 09 '18 at 14:02

score 3 · Accepted Answer · answered Jan 09 '18 at 12:13

The issue is with your output query I believe, although this answer doesn't verify the randomness it should show that it's pretty random.

Firstly, don't use Keywords such as COUNT as column names if you can help it. That's what has confused your output here.

Run this sample with 10000 runs and you should get a random set or results, but I'm not claiming it's fully randomised:

DECLARE @Random TABLE
    (
        Id INT ,
        Occurences INT
    );

DECLARE @TestData TABLE
    (
        Id INT
    );

DECLARE @runs INT = 0;

WHILE ( @runs <= 800 )
    BEGIN
        INSERT INTO @TestData
        VALUES ( @runs );
        SET @runs = @runs + 1;
    END;

SET @runs = 0;

WHILE ( @runs <= 10000 )
    BEGIN
        MERGE @Random AS target
        USING (   SELECT   TOP 10 Id
                  FROM     @TestData
                  ORDER BY ABS(CHECKSUM(NEWID())) % 100 ) AS SOURCE
        ON ( target.Id = SOURCE.Id )
        WHEN MATCHED THEN
            UPDATE SET target.Occurences = target.Occurences + 1
        WHEN NOT MATCHED THEN INSERT ( Id ,
                                       Occurences )
                              VALUES ( SOURCE.Id, 1 );
        SET @runs = @runs + 1;
    END;

SELECT   Id ,
         Occurences
FROM     @Random
ORDER BY Id;

NOTE: this should help you further your investigation but is not proof of the randomness. Further testing should be carried out.

Select random elements - SQL Server

1 Answers1