Qual è il modo migliore per ottenere un ordinamento casuale?

Il primo suggerimento di Pradeep Adiga, ORDER BY NEWID(), va bene e qualcosa che ho usato in passato per questo motivo.

Fate attenzione all’uso di RAND() – in molti contesti viene eseguito solo una volta per dichiarazione quindi ORDER BY RAND() non avrà alcun effetto (dato che state ottenendo lo stesso risultato da RAND() per ogni riga).

Per esempio:

SELECT display_name, RAND() FROM tr_person

restituisce ogni nome dalla nostra tabella delle persone e un numero “casuale”, che è lo stesso per ogni riga. Il numero varia ogni volta che si esegue la query, ma è lo stesso per ogni riga ogni volta.

Per mostrare che lo stesso accade con RAND() usato in una clausola ORDER BY, provo:

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

I risultati sono ancora ordinati per nome indicando che il campo di ordinamento precedente (quello che ci si aspettava fosse casuale) non ha effetto, quindi presumibilmente ha sempre lo stesso valore.

L’ordinamento per NEWID()funziona però, perché se NEWID() non fosse sempre rivalutato lo scopo degli UUID sarebbe rotto quando si inseriscono molte nuove righe in uno statemnt con identificatori unici come chiave, quindi:

SELECT display_name FROM tr_person ORDER BY NEWID()

ordina i nomi “a caso”.

Altri DBMS

Quanto sopra è vero per MSSQL (2005 e 2008 almeno, e se ricordo bene anche 2000). Una funzione che restituisce un nuovo UUID dovrebbe essere valutata ogni volta in tutti i DBMS NEWID() è sotto MSSQL ma vale la pena verificarlo nella documentazione e/o con i propri test. Il comportamento di altre funzioni dal risultato arbitrario, come RAND(), è più probabile che vari tra i DBMS, quindi controllate di nuovo la documentazione.

Inoltre ho visto che l’ordinamento dei valori UUID viene ignorato in alcuni contesti poiché il DB assume che il tipo non abbia un ordinamento significativo. Se trovate che questo sia il caso, lanciate esplicitamente l’UUID in un tipo di stringa nella clausola di ordinamento, o avvolgete qualche altra funzione intorno ad esso come CHECKSUM() in SQL Server (potrebbe esserci una piccola differenza di prestazioni anche da questo perché l’ordinamento sarà fatto su valori a 32 bit e non a 128 bit, anche se se il beneficio di questo supera il costo di eseguire CHECKSUM() per valore prima vi lascerò testare).

Nota a margine

Se volete un ordine arbitrario ma in qualche modo ripetibile, ordinate in base a qualche sottoinsieme relativamente incontrollato dei dati nelle righe stesse. Per esempio, uno di questi due restituirà i nomi in un ordine arbitrario ma ripetibile:

Gli ordinamenti arbitrari ma ripetibili non sono spesso utili nelle applicazioni, anche se possono essere utili nei test se volete testare del codice sui risultati in una varietà di ordini, ma volete essere in grado di ripetere ogni esecuzione allo stesso modo più volte (per ottenere risultati medi sui tempi in diverse esecuzioni, o per testare che una correzione che avete fatto al codice rimuova un problema o un’inefficienza precedentemente evidenziata da un particolare insieme di risultati in ingresso, o semplicemente per testare che il vostro codice sia “stabile” nel senso che restituisce lo stesso risultato ogni volta se gli vengono inviati gli stessi dati in un dato ordine).

Questo trucco può anche essere usato per ottenere risultati più arbitrari dalle funzioni, che non permettono chiamate non deterministiche come NEWID() all’interno del loro corpo. Di nuovo, questo non è qualcosa che probabilmente sarà spesso utile nel mondo reale, ma potrebbe essere utile se volete che una funzione restituisca qualcosa di casuale e “casuale-quasi” è abbastanza buono (ma fate attenzione a ricordare le regole che determinano quando le funzioni definite dall’utente vengono valutate, cioè di solito solo una volta per riga, o i vostri risultati potrebbero non essere quelli che vi aspettate/richieste).

Performance

Come EBarr sottolinea, ci possono essere problemi di performance con qualsiasi di questi metodi. Per più di qualche riga è quasi garantito che l’output venga inviato in spool a tempdb prima che il numero richiesto di righe venga letto nel giusto ordine, il che significa che anche se si sta cercando la top 10 si potrebbe trovare una scansione completa dell’indice (o peggio, della tabella) insieme a un enorme blocco di scrittura a tempdb. Per questo può essere di vitale importanza, come per la maggior parte delle cose, fare un benchmark con dati realistici prima di usarlo in produzione.