Conhecendo a diretiva de comportamento SET ANSI_NULLS no SQL Server

Dica – Diretiva de comportamento – SET ANSI_NULLS.

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.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP – Faculdade de Informática e Administração Paulista de São Paulo. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove – Campus São Roque. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1994 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator – Administrador de Banco de Dados – SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, entre outros recursos. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, etc. Trabalhei entre 2011 e 2017 como Administrador de Banco de Dados e Coordenador de TI no FIT – Instituto de Tecnologia da Flextronics, atualmente exerço a função de Professor Universitário na FATEC São Roque. CTO da Galvão Tecnologia, consultoria especializada em Gestão de TI, Administração de Servidores Windows Server, Bancos de Dados Microsoft SQL Server e Virtualização. Possuo titulação Oficial Microsoft MVP e reconhecimentos: MCC, MSTC e MIE.