Qual é a melhor maneira de obter uma ordenação aleatória?

Pradeep Adiga’s first suggestion, ORDER BY NEWID(), is fine and something I’ve used in the past for this reason for this reason.

Tenha cuidado ao usar RAND() – em muitos contextos só é executado uma vez por declaração pelo que ORDER BY RAND() não terá qualquer efeito (uma vez que está a obter o mesmo resultado de RAND() para cada linha).

Por exemplo:

SELECT display_name, RAND() FROM tr_person

devolve cada nome da nossa tabela pessoal e um número “aleatório”, que é o mesmo para cada linha. O número varia cada vez que executa a consulta, mas é o mesmo para cada linha de cada vez.

Para mostrar que o mesmo é o caso com RAND() usado numa cláusula ORDER BY, eu tento:

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

Os resultados ainda estão ordenados pelo nome indicando que o campo de ordenação anterior (aquele que se espera que seja aleatório) não tem efeito, por isso presumivelmente tem sempre o mesmo valor.

Ordenação por NEWID() funciona no entanto, porque se NEWID() nem sempre fosse reavaliado, o propósito dos UUIDs seria quebrado ao inserir muitas novas linhas numa declaração com identificadores únicos à medida que eles digitam, então:

SELECT display_name FROM tr_person ORDER BY NEWID()

ordena os nomes “aleatoriamente”.

Outros SGBD

O acima é verdade para MSSQL (2005 e 2008 pelo menos, e se bem me lembro 2000 também). Uma função que retorna um novo UUID deve ser avaliada sempre que em todos os SGBD NEWID() estiver sob MSSQL mas vale a pena verificar isso na documentação e/ou pelos seus próprios testes. O comportamento de outras funções de resultado arbitrário, como RAND(), é mais provável que varie entre SGBD, por isso verifique novamente a documentação.

Também já vi ordenação por valores UUID serem ignorados em alguns contextos, uma vez que o DB assume que o tipo não tem ordenação significativa. Se você achar que esse é o caso, explicitamente lance o UUID para um tipo de string na cláusula de ordenação, ou utilize alguma outra função como CHECKSUM() no SQL Server (pode haver uma pequena diferença de performance disso também, já que a ordenação será feita em valores de 32 bits e não de 128 bits, embora se o benefício disso supera o custo de execução CHECKSUM() por valor primeiro eu deixo você para testar).

Nota lateral

Se quiser uma ordenação arbitrária mas algo repetível, ordene por algum subconjunto relativamente descontrolado dos dados nas próprias linhas. Por exemplo, ou estes retornarão os nomes em uma ordem arbitrária, mas repetível:

Ordenações arbitrárias mas repetíveis não costumam ser úteis em aplicativos, embora possam ser úteis em testes se você quiser testar algum código nos resultados em uma variedade de ordens, mas quiser ser capaz de repetir cada execução da mesma maneira várias vezes (para obter resultados de tempo médio em várias execuções, ou testar que uma correção feita no código remove um problema ou ineficiência previamente destacada por um conjunto de resultados de input específico, ou apenas para testar que seu código é “estável”, ou seja, retorna o mesmo resultado a cada vez que envia os mesmos dados em uma determinada ordem).

Este truque também pode ser usado para obter resultados mais arbitrários de funções, que não permitem chamadas não-determinísticas como NEWID() dentro do seu corpo. Mais uma vez, isto não é algo que provavelmente seja útil no mundo real, mas pode vir a ser útil se quiser que uma função devolva algo aleatório e “aleatório-ish” é suficientemente bom (mas tenha cuidado para se lembrar das regras que determinam quando as funções definidas pelo utilizador são avaliadas, isto é, normalmente apenas uma vez por linha, ou os seus resultados podem não ser o que espera/exige).

Performance

Como o EBarr aponta, pode haver problemas de performance com qualquer um dos acima referidos. Para mais do que algumas linhas está quase garantido de ver a saída spooled out para tempdb antes do número de linhas pedido ser lido na ordem correcta, o que significa que mesmo que esteja à procura dos 10 primeiros pode encontrar um scan de índice completo (ou pior, scan de tabela) juntamente com um enorme bloco de escrita para tempdb. Por isso pode ser de vital importância, como na maioria das coisas, fazer benchmark com dados realistas antes de usar isto na produção.