Hvad er den bedste måde at få en tilfældig rækkefølge på?

Pradeep Adiga’s første forslag, ORDER BY NEWID(), er fint, og noget jeg tidligere har brugt af denne grund.

Vær forsigtig med at bruge RAND() – i mange sammenhænge udføres det kun én gang pr. statement, så ORDER BY RAND() vil ikke have nogen effekt (da du får det samme resultat ud af RAND() for hver række).

For eksempel:

SELECT display_name, RAND() FROM tr_person

giver hvert navn fra vores persontabel og et “tilfældigt” tal, som er det samme for hver række. Tallet varierer ganske vist hver gang, du kører forespørgslen, men er det samme for hver række hver gang.

For at vise, at det samme er tilfældet med RAND() anvendt i en ORDER BY-klausul, prøver jeg:

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

Resultaterne er stadig ordnet efter navnet, hvilket indikerer, at det tidligere sorteringsfelt (det, der forventes at være tilfældigt) ikke har nogen effekt, så det har formodentlig altid den samme værdi.

Ordning efter NEWID() virker dog, for hvis NEWID() ikke altid blev revurderet, ville formålet med UUID’er blive brudt, når man indsætter mange nye rækker i en statemnt med unikke identifikatorer som de nøgle, så:

SELECT display_name FROM tr_person ORDER BY NEWID()

ordner navnene “tilfældigt”.

Andre DBMS

Ovenstående gælder for MSSQL (2005 og 2008 i hvert fald, og hvis jeg husker rigtigt 2000 også). En funktion der returnerer et nyt UUID bør evalueres hver gang i alle DBMS’er NEWID() er under MSSQL, men det er værd at verificere dette i dokumentationen og/eller ved egne tests. Opførslen af andre arbitrært-resultat-funktioner, som RAND(), er mere tilbøjelig til at variere mellem DBMS’er, så igen tjek dokumentationen.

Også har jeg set, at rækkefølge efter UUID-værdier bliver ignoreret i nogle sammenhænge, da DB’en antager, at typen ikke har nogen meningsfuld rækkefølge. Hvis du finder, at dette er tilfældet, skal du eksplicit kaste UUID’en til en strengtype i bestillingsklausulen eller pakke en anden funktion omkring den som CHECKSUM() i SQL Server (der kan også være en lille ydelsesforskel ved dette, da bestillingen vil blive udført på en 32-bit værdier og ikke en 128-bit, men om fordelen ved det opvejer omkostningerne ved at køre CHECKSUM() pr. værdi først, skal jeg lade dig teste).

Side Note

Hvis du ønsker en vilkårlig, men nogenlunde gentagelig rækkefølge, så bestil efter en eller anden relativt ukontrolleret delmængde af dataene i selve rækkerne. F.eks. vil en af disse to returnerer navnene i en vilkårlig, men gentagelig rækkefølge:

Arbitrær men gentagelig rækkefølge er ikke ofte nyttig i applikationer, men kan være nyttig i test, hvis du ønsker at teste noget kode på resultater i forskellige rækkefølger, men ønsker at kunne gentage hver kørsel på samme måde flere gange (for at få gennemsnitlige timingresultater over flere kørsler, eller for at teste, at en rettelse, du har foretaget i koden, fjerner et problem eller en ineffektivitet, der tidligere blev fremhævet af et bestemt input-resultatsæt, eller bare for at teste, at din kode er “stabil”, idet den returnerer det samme resultat hver gang, hvis den får de samme data i en given rækkefølge).

Dette trick kan også bruges til at få mere arbitrære resultater fra funktioner, som ikke tillader ikke-deterministiske kald som NEWID() inden for deres krop. Igen er dette ikke noget, der sandsynligvis ikke vil være ofte nyttigt i den virkelige verden, men det kan være praktisk, hvis du vil have en funktion til at returnere noget tilfældigt, og “tilfældigt” er godt nok (men vær omhyggelig med at huske de regler, der bestemmer, hvornår brugerdefinerede funktioner evalueres, dvs. normalt kun én gang pr. række, ellers bliver dine resultater måske ikke, hvad du forventer/kræver).

Performance

Som EBarr påpeger, kan der være performanceproblemer med nogen af ovenstående. For mere end et par rækker er du næsten garanteret at se output spoolet ud til tempdb før det ønskede antal rækker bliver læst tilbage i den rigtige rækkefølge, hvilket betyder at selv hvis du leder efter top 10 kan du opleve at en fuld indeks scanning (eller værre, tabel scanning) sker sammen med en stor blok af skrivning til tempdb. Derfor kan det være livsvigtigt, som med de fleste ting, at benchmarke med realistiske data før man bruger dette i produktion.