Conhecendo a diretiva de comportamento SET ANSI_NULLS no SQL Server

A dica de hoje tem o objetivo de apresentar a diretiva de comportamento SET ANSI_NULLS, existente no Microsoft SQL Server á várias versões e edições, e que continua a ser utilizada e disponível no SQL Server 2008 R2.

Esta diretiva tem como função especificar o comportamento compatível ISO dos operadores de comparação Igual a (=) e Diferente de (<>) quando usados com valores nulos.

Vale ressaltar que sua utilização deve ser planejada, pois em futuras versões do SQL Server seu comportamento padrão será definido como On, aplicativos que definam explicitamente a opção como OFF gerarão um erro. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente.

Sintaxe:

SET ANSI_Nulls {On | Off}
Quando o SET ANSI_NULLS for ON, todas as comparações em relação a um valor nulo serão avaliadas como UNKNOWN(desconhecida). Caso o SET ANSI_NULLS for OFF, as comparações de todos os dados em relação a um valor nulo serão avaliadas como TRUE(verdadeiras). 
 
Por outro lado, se o SET ANSI_NULLS não for especificado, a configuração da opção ANSI_NULLS do banco de dados atual será aplicada. Aoutilizarmos SET ANSI_NULLS ON o SQL Server realizará uma comparação somente se um dos operandos dessa comparação for uma variável NULL ou um NULL literal. 
 
Caso os dois lados da comparação forem colunas ou expressões compostas, a configuração não afetará a comparação. Para que um script funcione conforme pretendido, independentemente da opção de banco de dados ANSI_NULLS ou da configuração de SET ANSI_NULLS, podemos utilizar os operadores IS NULL e IS NOT NULL nas comparações que possam conter valores nulos.
SET ANSI_NULLS deve ser definido como ON para executar consultas distribuídas,  também deve ser ON quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Se SET ANSI_NULLS for OFF, qualquer instrução CREATE, UPDATE, INSERT e DELETE nas tabelas com índices em colunas computadas ou exibições indexadas irá falhar. O SQL Server retornará um erro que lista todas as opções SET que violam os valores requeridos. Além disso, ao executar uma instrução SELECT, se SET ANSI_NULLS for OFF, o SQL Server irá ignorar os valores de índice nas exibições ou colunas computadas e resolverá a operação selecionada como se não houvessem tais índices nas tabelas ou exibições.

Observação:
ANSI_NULLS é uma das sete opções SET que devem ser definidas como valores requeridos ao lidar com índices em colunas computadas ou exibições indexadas. As opções ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER e CONCAT_NULL_YIELDS_NULL também devem ser definidas como ON, e NUMERIC_ROUNDABORT deve ser definida como OFF.

Outro detalhe importante, ao utilizar o SET ANSI_NULLS em conjunto com o driver ODBC do SQL Server Native Client e o OLE DB Provider for SQL Server do SQL Server Native Client definem automaticamente ANSI_NULLS como ON durante a conexão.

Essa configuração pode ser definida nas fontes de dados ODBC, nos atributos de conexão ODBC ou nas propriedades de conexão OLE DB definidos no aplicativo antes de conectar a uma instância do SQL Server. O padrão para SET ANSI_NULLS é OFF.

O SQL Server utiliza uma outra diretiva de comportamento chamada SET ANSI_DEFAULTS, envolvida diretamente na forma de uso do SET ANSI_NULLS. Quando SET ANSI_DEFAULTS é ON, SET ANSI_NULLS está habilitado. A configuração de ANSI_NULLS é definida durante a execução ou em tempo de execução e não no momento da análise.

Exemplo:

O exemplo a seguir usa os operadores de comparação Igual a (=) e Diferente de (<>) para fazer comparações com valores NULL e não nulos em uma tabela. O exemplo também mostra que IS NULL não é afetado pela configuração SET ANSI_NULLS.

— Criando a Tabela T1 e Inserindo Dados —

CREATE TABLE t1 (a INT NULL)

INSERT INTO t1 values (NULL)

INSERT INTO t1 values (0)

INSERT INTO t1 values (1)

GO

— Exibindo uma mensagem padrão e executando o comando Select —

PRINT ‘Testing default setting’

DECLARE @varname int

SELECT @varname = NULL

SELECT *FROM t1

WHERE a = @varname

SELECT * FROM t1

WHERE a <> @varname

SELECT * FROM t1

WHERE a IS NULL

GO

— Utilizando o SET ANSI_NULLS to ON —

PRINT ‘Testing ANSI_NULLS ON’

SET ANSI_NULLS ON

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT * FROM t1

WHERE a = @varname

SELECT *FROM t1

WHERE a <> @varname

SELECT *FROM t1

WHERE a IS NULL

GO

— Utilizando o SET ANSI_NULLS to OFF —

PRINT ‘Testing SET ANSI_NULLS OFF’

SET ANSI_NULLS OFF

GO

DECLARE @varname int

SELECT @varname = NULL

SELECT *FROM t1

WHERE a = @varname

SELECT *FROM t1

WHERE a <> @varname

SELECT *FROM t1

WHERE a IS NULL

GO

— Excluíndo a Tabela T1 —
DROP TABLE t1

Acredito que este simples exemplo deve ter ilustrado como o SQL Server realiza a análise e definição da forma de uso e comportamento ao se utilizar a diretiva SET ANSI_NULLS. Espero mais uma vez ter apresentado um recurso simples, prático e de muito importância principalmente quando realizamos comparações entre valores e condições matématicas.

Agradeço mais uma vez a sua atenção, vou ficando por aqui.

Até mais.