Problema de ordenação padrão da instrução SELECT perfeitamente reproduzível

Question

Recentemente, tenho perseguido um problema com o db de um cliente ... a solução encontrada, mas impossível de recriar.

Essencialmente, estamos fazendo um

Select * from mytable where ArbitraryColumn = 75

Em que MyTable possui uma coluna Identity, chamada 'MyIndentityColumn' - incrementada em um em cada inserção. Naturalmente, e normalmente eu diria que a ordem retornada seria a ordem em que eles são inseridos (suposição incorreta, mas que foi forçada a mim, através de um aplicativo herdado - que foi corrigido).

Essencialmente, gostaria de sugestões de porque o banco de dados, quando restaurado para minha máquina local (mesmo sistema operacional, mesma versão do servidor SQL - 200 sp3) mesmo agrupamento e mesma instância de backup restaurada nele, como um teste DB no site do cliente.

Quando executo a seleção acima, eu as obtenho em ordem de inserção (ou seja, a coluna de identidade é ordenada ascendente). No cliente, parece aleatório (mas a mesma ordem 'aleatória' de cada vez) ...

Alguns outros pontos:

1. Eu tenho o mesmo agrupamento no meu servidor de teste que o cliente
2. O mesmo backup de banco de dados restaurado para um teste que só eu posso acessar
3. Mesma versão do servidor SQL e service pack
4. Same OS
5. O banco de dados de teste é um novo banco de dados - novo log e MDF ...

Eu tenho o problema 'resolvido' adicionando uma ordem explícita por cláusula, mas eu quero informar sobre a causa do problema, dada a natureza exata das minhas tentativas de recriá-lo, tornando-o fútil e perfeitamente recursível no servidor cliente. .

Agradecemos antecipadamente

Dave

    

Answer 1

O pedido de um conjunto de registros é determinado somente pelo ORDER BY mais externo.

Para evitar dúvidas: não há uma ordem de classificação padrão

A ordem "reproduzível" mencionada reflete apenas o modo como o SQL Server lê os dados no plano de execução para seu servidor em esse tempo . O mesmo service pack? Mesma edição? Mesmo número de CPUs (incluindo HT)? Exatamente as mesmas opções SET?

Se o plano mudar, a ordem provavelmente será alterada também. Não tinha nada a ver com ordem de inserção ou ordem de índice ou ordem de localização de disco.

Esta questão pertence ao StackOverflow e foi respondida antes:

• link
• link
• etc

No MSDN, Classificando linhas com ORDER BY

ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition

De Blog da Conor ,

Does the SQL Server Query Optimizer consider index fragmentation in plan selection?

No, it does not directly care.

    

Answer 2

A ordem em que os registros são retornados depende da sua indexação. Três coisas entram em jogo:

• qual é o índice usado na consulta e sua coluna de identidade é a parte principal do índice.
• Quão fragmentado é o índice usado. Se for altamente fragmentado, os registros certamente serão devolvidos fora de ordem.
• É o índice em que você está usando o índice clusterizado nessa tabela

A única maneira de garantir a ordem dos registros retornados é usar uma instrução ORDER BY. Se você estiver dependendo da ordem específica do conjunto de registros retornado, deverá usar uma instrução ORDER BY.

    

Answer 3

Após uma extensa investigação, encontramos o problema da rota ...

O cliente tinha um índice adicional em uma tabela, que estava jogando fora de sincronia, mas apenas em seu servidor. Quando testado localmente, um backup do LDF / MDF ou uma restauração em um novo banco de dados - o problema não foi exibido.

Na máquina cliente, no mdf / mdf original, depois de ter sido feito backup e restaurado, ou em um novo banco de dados que foi restaurado, a edição é exibida 100% confiável.

Como prova de que o índice foi a causa, poderíamos remover e adicionar novamente o índice e ver o comportamento indesejado acontecendo e, em seguida, não acontecendo.