Prática recomendada de SQL para lidar com a ordem de sorting padrão

Um monte de código SQL que eu li, parece que o desenvolvedor assume que a ordem de sorting padrão sempre é válida. Por exemplo, ao criar uma lista de seleção de HTML, eles apenas selecionariam SELECT id, name FROM table sem emitir uma ORDER BY .

De minha própria experiência, parece que o dbms sempre ordena os dados usando o FIFO se nenhuma ORDER BY for fornecida e nenhum índice. No entanto, o pedido não é garantido. Mas eu nunca vi um dbms reordenando dados se não houver mudanças na tabela.

Você já experimentou um dbms selecionando dados em uma ordem não determinística se não houver alteração na tabela?

A melhor prática é sempre colocar uma cláusula ORDER BY?

Não há ordem de sorting padrão. Mesmo se a tabela tiver um índice clusterizado, não é garantido que você obtenha os resultados nessa ordem. Você deve usar uma cláusula order by se desejar um pedido específico.

Como os outros pôsteres mencionam, se você não especificar uma ordem de sorting, o padrão SQL diz que os resultados podem estar em qualquer ordem que o processador de consultas achar mais conveniente e eficiente.

Digamos que você faça um SELECT desordenado simples para todas as linhas de uma tabela CUSTOMER, que não possui índices e nenhuma chave primária. É bem possível, e até provável, que o processador de consultas faça uma varredura direta na tabela e produza as linhas na ordem em que foram originalmente inseridas (dando-lhe o comportamento FIFO que você viu).

Se você adicionar um índice nos campos STATE e CITY (nessa ordem) e, em seguida, consultar WHERE STATE = 'NY' o processador de consultas poderá decidir se é mais eficiente verificar as inputs de índice para STATE = ‘NY’ em vez de faça uma varredura completa na tabela. Neste caso, provavelmente materializaria as linhas na ordem STATE, CITY.

Mesmo isso não é certo. Por exemplo, se o processador de consultas reuniu statistics que mostram que quase todos os valores STATE em sua tabela são ‘NY’ (talvez porque o database é para um negócio de aluguel de equipamentos baseado em Albany), ele pode decidir que a verificação da tabela é realmente mais barata do que a varredura de índice, e você verá FIFO novamente.

É uma boa ideia aprender algumas noções básicas sobre como seu database planeja suas consultas. Você pode usar a instrução EXPLAIN para ver como seu SGBD executaria uma determinada consulta e usá-la para otimizar sua consulta, em alguns casos por ordens de magnitude. Esta é uma área fascinante e útil para aprender.

Se você quiser que os dados sejam ordenados consistentemente, sim – você tem que usar ORDER BY .

Sim. Não existe uma “ordem padrão” sem um ORDER BY e não há garantia de que você obterá os dados de volta no FIFO / LIFO ou em qualquer outro pedido.

No que diz respeito aos desenvolvedores que usam “SELECT id, name FROM table”, eles são ineptos ou não se importam com a ordem em que algo aparece.

Nenhum RDBMS sério garante qualquer pedido, a menos que você especifique um ORDER BY explícito.

Qualquer outra coisa é apenas pura sorte ou anectodal – se você quer ordem, você tem que especificar ORDER BY – nada disso.

Se você deseja que os dados sejam ordenados, a única maneira de garantir qualquer coisa (com todos os principais sistemas RDBMS que eu conheço, definitivamente Sql Server e Oracle) é include uma cláusula ORDER BY. O FIFO não tem absolutamente nada a ver com os dados do pedido serem retornados sem uma cláusula ORDER BY, e não há um conceito de qualquer tipo de ordem de sorting DEFAULT. A chamada ordem de sorting DEFAULT é basicamente no entanto o mecanismo obtém os dados, que podem estar em literalmente qualquer ordem com base em índices, dados em cache, consultas em execução simultâneas, carga no servidor, etc., etc.

Este outro thread stackoverflow está basicamente cobrindo o mesmo conceito em relação ao Sql Server, AlexK blogou um repo para demonstrar o comportamento.

Até mesmo uma consulta simples como a SELECT ... FROM table pode retornar dados em várias ordens. Eu sei que isso é verdade em teoria, eu sei que isso é verdade na prática, e tenho visto muitos casos em que a ordem muda entre execuções subseqüentes, mesmo quando nenhuma alteração de dados ocorre na tabela.

Um exemplo típico de alterações de pedidos entre execuções é quando a consulta é executada usando um plano paralelo. Como os operadores paralelos retornam dados à medida que os encadeamentos subjacentes o produzem, a ordem das linhas no resultado varia entre cada execução. Esta situação faz com que mesmo o simples SELECT no seu exemplo retorne resultados completamente diferentes cada vez que é executado.

Na minha experiência com SQL, na maioria das vezes eu não especifico um ORDER BY em SQL, porque os conjuntos de registros são exibidos em um controle de tipo de grade ” client-side ” etc. onde a ordenação dinâmica é suportada – neste caso ordenando por SQL é desnecessário , pois será verificado do lado do cliente de qualquer maneira.

Isso também é feito no lado do cliente porque a mesma consulta pode ser usada para exibir os dados em locais diferentes em pedidos diferentes.

Portanto, é apenas uma boa prática colocar em um ORDER BY, quando

  • A ordem dos dados é importante; e
  • A sorting é mais eficiente no nível do database.

ou seja, se o desenvolvedor do front end estiver “classificando-o novamente” de qualquer maneira, então não faz sentido, já que é improvável que ele economize tempo total de processamento.

Talvez os escritores dessas consultas SQL que você está lendo não se importem com a ordem dos dados retornados. A melhor prática é usá-lo onde você precisa garantir a ordem dos resultados retornados!

Eu estou escrevendo isso no caso, se alguém gostaria de usar isso como eu fiz.

Bem, estou recebendo uma ordem de sorting padrão satisfatória, digamos, para tabelas de log, com sorting em Index. Por exemplo, normalmente estou interessado nas últimas linhas da tabela de log (LIFO), portanto, faço DateTime DESC como ordem. Eu também tentei por diversão adicionar Index no outro campo (inteiro) ao lado da chave primária e funcionou.

 CREATE TABLE [dbo].[tableA]([DateTime] [datetime] NOT NULL, CONSTRAINT [PK_tableA] PRIMARY KEY CLUSTERED ([DateTime] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] 

Ou no SSMS …

insira a descrição da imagem aqui