Dicas de T-SQL – Cuidado com LOOP, variável NULL e ANSI_NULLS

Fala Pessoal,

Nesse post quero compartilhar com vocês um exemplo de um LOOP que tem um comportamento inesperado devido à uma variável com NULL e a influência da opção ANSI_NULLS.

OBS: Desde já agradeço ao Rodrigo Ribeiro pela discussão e apoio nas sugestões para esse post.


ANSI_NULLS:

Esse conceito será importante na execução do teste. Não vou entrar em muitos detalhes sobre ele, caso você queira saber um pouco mais, de uma olhada nesse post do Dirceu Resende.

https://www.dirceuresende.com/blog/sql-server-heterogeneous-queries-require-the-ansi_nulls-and-ansi_warnings-options-to-be-set-for-the-connection/

Vou mostrar a mudança no comportamento de uma query ao alterar essa opção, que por padrão já vem habilitada (SET ANSI_NULLS ON) no SQL Server. A recomendação é que essa opção NÃO seja alterada.


Teste Loop:

Vamos iniciar a explicação do nosso teste. Primeiro eu crio uma tabela chamada “Cliente ” com alguns registros, sendo um deles com o nome “NULL” de propósito para gerar a inconsistência mais à frente na utilização do LOOP.

O teste que vamos fazer é o seguinte:

  • Iremos fazer um loop em cada registro da tabela Cliente. Aqui criei uma tabela temporária com todos os registros;
  • Depois eu pego o menor ID e atualizo a variável “@LOOP_NOME”;
  • Agora eu faço um novo filtro na tabela temporária utilizando essa variável e atualizo outras duas variáveis e exibo os seus valores. OBS: atenção nessa parte, pois ela será a causa do problema;
  • Usamos a variável “LOOP_SOMA” para somar o ID de cada registro.
  • Por fim, excluo o ID da tabela temporária e vou para o próximo registro.

Teste 1 – SET ANSI_NULLS ON:

Nesse primeiro teste, vamos usar a opção “SET ANSI_NULLS ON”, que já vem habilitada por padrão no SQL Server. Repare que no resultado abaixo o “Fabricio Lima” apareceu duas vezes e esse comportamento não era esperado.

“Mas que bruxaria é essa Luiz?!?!?” Calma que eu já vou explicar…

Vamos analisar com mais calma o momento que as variáveis do LOOP são atualizadas.

Na iteração do LOOP_ID = 3, as outras variáveis são atualizadas com “Fabricio Lima” e “6”.

Agora preste bastante atenção! Na iteração do LOOP_ID = 4, antes de fazer o SELECT, as variáveis ainda estão com os valores “Fabricio Lima” e “6”. Já a variável LOOP_NAME está com o valor NULL.

“Mas Luiz, você mostrou que existe um registro com o nome NULL, porque diabos ele não foi retornado?”

Pois eh, nesse ponto que está o pulo do gato. Repare que existe uma pequena diferença entre “NOME = NULL” e “NOME IS NULL”. Por padrão, “NULL” seria uma ausência de valor, por esse motivo, qualquer coisa igual a NULL nunca irá retornar nada e por isso que normalmente utilizamos o “IS NULL” ao desenvolver as nossas queries.

Ou seja: “NOME = NULL” <> “NOME IS NULL”

Segue um teste simples para validar isso. Quando “NOME = NULL” não irá retornar nada. No nosso caso, foi exatamente isso que aconteceu, ao fazer o SELECT e não retornar nada, os valores das variáveis não foram alterados e por isso ficaram iguais aos da iteração anterior do loop. Por esse motivo, o “Fabricio Lima” apareceu duas vezes no resultado.


Teste 2 – SET ANSI_NULLS OFF:

Agora iremos utilizar a opção “SET ANSI_NULLS OFF”. Repare que dessa vez o resultado é diferente e o “Fabricio Lima” apareceu apenas uma vez. A soma total também ficou diferente, antes era 11 e agora ficou 15.

“Ferrou Luiz, não estou entendendo mais nada!!!” Calma, não desiste amigo…

No teste abaixo conseguimos observar a diferença no comportamento ao utilizar o “SET ANSI_NULLS OFF”. Dessa vez, quando “NOME = NULL” irá retornar o registro e fazer a soma também! Nesse caso, como o registro foi retornado, as variáveis foram atualizadas normalmente. Com isso, o “Fabricio Lima” apareceu apenas uma vez, conforme era esperado.

OBS: Cuidado que essa alteração também pode afetar o resultado dos JOINs!


Teste 3 – Limpar Variáveis do Loop:

Nesse último teste vou mostrar uma boa prática que é SEMPRE limpar as variáveis que são utilizadas no LOOP. Vamos usar o “ANSI_NULLS ON” para ter o comportamento padrão e descomentar a linha que está no início do LOOP para limpar as variáveis a cada iteração.

Repare que dessa vez o “Fabricio Lima” não se repetiu, pois o valor da variável foi atualizado no início do LOOP para evitar esse problema. O valor da soma total também ficou diferente do Teste 2 com a opção “ANSI_NULLS OFF”.


Conclusão:

Portanto, as ideias principais desse post são:

  • Tenha cuidado com a opção “SET ANSI_NULLS ON/OFF”, pois isso pode afetar o resultado da sua query. Se for possível, evite alterar isso!
  • O padrão do SQL Server é utilizar a opção “SET ANSI_NULLS ON”.
  • Utilize a boa prática de SEMPRE limpar as variáveis no início do LOOP para evitar um resultado inconsistente.

Cursos – Dicas de Performance / T-SQL:

O Fabiano Amorim gravou alguns Cursos TOPs com várias outras Dicas de Performance e T-SQL. #ficaadica

25 Dicas de Performance no SQL Server – Parte 1

+25 Dicas de Performance no SQL Server – Parte 2


Referências:

Segue abaixo mais algumas referências:

https://www.dirceuresende.com/blog/sql-server-heterogeneous-queries-require-the-ansi_nulls-and-ansi_warnings-options-to-be-set-for-the-connection/

https://www.dirceuresende.com/blog/os-comandos-set-do-sql-server/

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver15


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

Deixe uma resposta