Wat is de beste manier om een willekeurige volgorde te krijgen?

Pradeep Adiga’s eerste suggestie, ORDER BY NEWID(), is prima en iets dat ik in het verleden heb gebruikt om deze reden.

Wees voorzichtig met het gebruik van RAND() – in veel contexten wordt het slechts eenmaal per statement uitgevoerd, zodat ORDER BY RAND() geen effect heeft (omdat u voor elke rij hetzelfde resultaat uit RAND() krijgt).

Voorbeeld:

SELECT display_name, RAND() FROM tr_person

retourneert elke naam uit onze persoonstabel en een “willekeurig” getal, dat voor elke rij hetzelfde is. Het getal varieert elke keer dat u de query uitvoert, maar is elke keer hetzelfde voor elke rij.

Om te laten zien dat hetzelfde het geval is met RAND() gebruikt in een ORDER BY clausule, probeer ik:

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

De resultaten zijn nog steeds geordend op naam, wat aangeeft dat het eerdere sorteerveld (degene waarvan verwacht wordt dat het willekeurig is) geen effect heeft en dus vermoedelijk altijd dezelfde waarde heeft.

Ordening op NEWID() werkt wel, want als NEWID() niet altijd opnieuw zou worden beoordeeld, zou het doel van UUIDs worden doorbroken bij het invoegen van veel nieuwe rijen in één statemnt met unieke identifiers als hun sleutel, dus:

SELECT display_name FROM tr_person ORDER BY NEWID()

ordent de namen “willekeurig”.

Andere DBMS

Het bovenstaande geldt voor MSSQL (2005 en 2008 althans, en als ik me goed herinner 2000 ook). Een functie die een nieuwe UUID teruggeeft zou elke keer geëvalueerd moeten worden in alle DBMS’en NEWID() is onder MSSQL, maar het is de moeite waard om dit te verifiëren in de documentatie en/of door uw eigen tests. Het gedrag van andere arbitrair-resultaat functies, zoals RAND(), zal waarschijnlijk meer variëren tussen DBMS’en, dus controleer opnieuw de documentatie.

Ook heb ik gezien dat ordening door UUID waarden in sommige contexten wordt genegeerd omdat de DB aanneemt dat het type geen zinvolle ordening heeft. Als je merkt dat dit het geval is, cast dan expliciet de UUID naar een string type in de order clause, of wikkel er een andere functie omheen zoals CHECKSUM() in SQL Server (er kan ook een klein performance verschil zijn omdat de ordening wordt gedaan op een 32-bit waarde en niet op een 128-bit, maar of het voordeel daarvan opweegt tegen de kosten van het eerst uitvoeren van CHECKSUM() per waarde laat ik aan jou over om te testen).

Noot

Als je een arbitraire maar enigszins herhaalbare volgorde wilt, orden dan op een relatief ongecontroleerde subset van de gegevens in de rijen zelf. Bijvoorbeeld een van deze geeft de namen in een willekeurige, maar herhaalbare volgorde:

Arbitraire maar herhaalbare rangschikkingen zijn niet vaak nuttig in toepassingen, maar kunnen nuttig zijn bij het testen als u code wilt testen op resultaten in verschillende volgordes, maar elke run meerdere malen op dezelfde manier wilt herhalen (om gemiddelde timingsresultaten te krijgen over meerdere runs, of om te testen of een fix die u in de code hebt aangebracht een probleem of inefficiëntie oplost die eerder door een bepaalde input resultset naar voren kwam, of gewoon om te testen of uw code “stabiel” is in die zin dat hij elke keer hetzelfde resultaat geeft als u dezelfde gegevens in een bepaalde volgorde stuurt).

Deze truc kan ook worden gebruikt om meer willekeurige resultaten te krijgen van functies, die geen niet-deterministische aanroepen zoals NEWID() toestaan binnen hun body. Nogmaals, dit is niet iets dat waarschijnlijk vaak nuttig zal zijn in de echte wereld, maar het kan van pas komen als je een functie wilt om iets willekeurigs terug te geven en “willekeurig” is goed genoeg (maar wees voorzichtig om de regels te onthouden die bepalen wanneer door de gebruiker gedefinieerde functies worden geëvalueerd, d.w.z. meestal slechts eenmaal per rij, of je resultaten zijn misschien niet wat je verwacht/eist).

Prestaties

Zoals EBarr opmerkt, kunnen er prestatieproblemen zijn met elk van de bovenstaande. Voor meer dan een paar rijen is het bijna gegarandeerd dat de uitvoer naar tempdb wordt gespoold voordat het gevraagde aantal rijen in de juiste volgorde wordt teruggelezen, wat betekent dat zelfs als je op zoek bent naar de top 10, je een volledige index scan (of erger nog, tabel scan) zou kunnen zien gebeuren samen met een enorm blok van schrijven naar tempdb. Daarom kan het van vitaal belang zijn, zoals met de meeste dingen, om te benchmarken met realistische data alvorens dit in productie te gebruiken.