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.

USE Teste

CREATE TABLE Cliente (
	ID INT NOT NULL IDENTITY(1,1),
	NOME VARCHAR(100) NULL
)

INSERT INTO Cliente
VALUES('Luiz Lima'), ('Dirceu Resende'), ('Fabricio Lima'), (NULL), ('Rodrigo Ribeiro') 

SELECT * FROM Cliente

USE Teste

CREATE TABLE Cliente (

ID INT NOT NULL IDENTITY(1,1),

NOME VARCHAR(100) NULL

)

INSERT INTO Cliente

VALUES('Luiz Lima'), ('Dirceu Resende'), ('Fabricio Lima'), (NULL), ('Rodrigo Ribeiro')

SELECT * FROM Cliente

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.

SET ANSI_NULLS ON

IF(OBJECT_ID('tempdb..#TEMP_Cliente') IS NOT NULL)
	DROP TABLE #TEMP_Cliente

SELECT * 
INTO #TEMP_Cliente
FROM Cliente

DECLARE @LOOP_ID INT, @LOOP_NOME VARCHAR(100), @LOOP_RESULTADO VARCHAR(100), @LOOP_SOMA INT = 0

WHILE EXISTS (
	SELECT ID FROM #TEMP_Cliente
)
BEGIN
	-- BOA PRÁTICA: LIMPAR AS VARIAVEIS NO INICIO DO LOOP
	-- SELECT @LOOP_ID = NULL, @LOOP_NOME = NULL, @LOOP_RESULTADO = NULL

	-- BUSCA O MENOR ID DA TABELA TEMPORARIA
	SELECT @LOOP_ID = MIN(ID)
	FROM #TEMP_Cliente

	-- SETA A VARIAVEL COM O NOME DO CLIENTE
	SELECT @LOOP_NOME = NOME
	FROM #TEMP_Cliente
	WHERE ID = @LOOP_ID
	
	SELECT 
		@LOOP_RESULTADO = NOME,
		@LOOP_SOMA += ID
	FROM #TEMP_Cliente
	WHERE NOME = @LOOP_NOME

	SELECT @LOOP_ID AS LOOP_ID, @LOOP_NOME AS LOOP_NOME, @LOOP_RESULTADO AS LOOP_RESULTADO, @LOOP_SOMA AS SOMA_LOOP

	-- EXCLUI O ID DA TABELA TEMPORARIA
	DELETE #TEMP_Cliente
	WHERE ID = @LOOP_ID
END

SET ANSI_NULLS ON

IF(OBJECT_ID('tempdb..#TEMP_Cliente') IS NOT NULL)

DROP TABLE #TEMP_Cliente

SELECT *

INTO #TEMP_Cliente

FROM Cliente

DECLARE @LOOP_ID INT, @LOOP_NOME VARCHAR(100), @LOOP_RESULTADO VARCHAR(100), @LOOP_SOMA INT = 0

WHILE EXISTS (

SELECT ID FROM #TEMP_Cliente

)

BEGIN

-- BOA PRÁTICA: LIMPAR AS VARIAVEIS NO INICIO DO LOOP

-- SELECT @LOOP_ID = NULL, @LOOP_NOME = NULL, @LOOP_RESULTADO = NULL

-- BUSCA O MENOR ID DA TABELA TEMPORARIA

SELECT @LOOP_ID = MIN(ID)

FROM #TEMP_Cliente

-- SETA A VARIAVEL COM O NOME DO CLIENTE

SELECT @LOOP_NOME = NOME

FROM #TEMP_Cliente

WHERE ID = @LOOP_ID

SELECT

@LOOP_RESULTADO = NOME,

@LOOP_SOMA += ID

FROM #TEMP_Cliente

WHERE NOME = @LOOP_NOME

SELECT @LOOP_ID AS LOOP_ID, @LOOP_NOME AS LOOP_NOME, @LOOP_RESULTADO AS LOOP_RESULTADO, @LOOP_SOMA AS SOMA_LOOP

-- EXCLUI O ID DA TABELA TEMPORARIA

DELETE #TEMP_Cliente

WHERE ID = @LOOP_ID

END

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.

-- INICIO LOOP...

SELECT 
	@LOOP_RESULTADO = NOME,
	@LOOP_SOMA += ID
FROM #TEMP_Cliente
WHERE NOME = @LOOP_NOME

-- FIM LOOP...

-- INICIO LOOP...

SELECT

@LOOP_RESULTADO = NOME,

@LOOP_SOMA += ID

FROM #TEMP_Cliente

WHERE NOME = @LOOP_NOME

-- FIM LOOP...

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.

SET ANSI_NULLS ON

SELECT * 
FROM #TEMP_Cliente
WHERE NOME = NULL

SELECT * 
FROM #TEMP_Cliente
WHERE NOME IS NULL

SET ANSI_NULLS ON

SELECT *

FROM #TEMP_Cliente

WHERE NOME = NULL

SELECT *

FROM #TEMP_Cliente

WHERE NOME IS NULL

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!

SET ANSI_NULLS OFF

SELECT * 
FROM #TEMP_Cliente
WHERE NOME = NULL

SELECT * 
FROM #TEMP_Cliente
WHERE NOME IS NULL

SET ANSI_NULLS OFF

SELECT *

FROM #TEMP_Cliente

WHERE NOME = NULL

SELECT *

FROM #TEMP_Cliente

WHERE NOME IS NULL

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.

SET ANSI_NULLS ON

-- BOA PRÁTICA: LIMPAR AS VARIAVEIS NO INICIO DO LOOP
SELECT @LOOP_ID = NULL, @LOOP_NOME = NULL, @LOOP_RESULTADO = NULL

SET ANSI_NULLS ON

-- BOA PRÁTICA: LIMPAR AS VARIAVEIS NO INICIO DO LOOP

SELECT @LOOP_ID = NULL, @LOOP_NOME = NULL, @LOOP_RESULTADO = NULL

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

ANSI_NULLS:

Teste Loop:

Teste 1 – SET ANSI_NULLS ON:

Teste 2 – SET ANSI_NULLS OFF:

Teste 3 – Limpar Variáveis do Loop:

Conclusão:

Cursos – Dicas de Performance / T-SQL:

Referências:

Compartilhe isso:

Deixe uma respostaCancelar resposta