CONJUNTO ANSI_NULLS (Transact-SQL)

2025-05-08

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Especifica o comportamento em conformidade com ISO dos operadores de comparação Equals (=) e Not Equal To (<>) quando eles são usados com NULL valores no SQL Server.

SET ANSI_NULLS ON - Avalia ambos {expression} = NULL e {expression} <> NULL como False se o valor {expression} fosse NULL. Esse comportamento é compatível com ANSI.
SET ANSI_NULLS OFF- Avalia como e como se o valor {expression} = NULL fosse True.{expression} <> NULLFalse{expression}NULL Esse comportamento não é recomendado, pois os NULL valores não devem ser comparados usando = e <> operadores.

Observação

SET ANSI_NULLS OFF e a opção ANSI_NULLS OFF de banco de dados foi preterida. A partir do SQL Server 2017 (14.x), ANSI_NULLS é sempre definido como ON. Recursos preteridos não devem ser usados em aplicativos novos. Para obter mais informações, consulte Recursos preteridos do Mecanismo de Banco de Dados no SQL Server 2017.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server, Pool de SQL sem servidor no Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Sintaxe para Azure Synapse Analytics e PDW (Analytics Platform System)

SET ANSI_NULLS ON

Comentários

Quando ANSI_NULLS estiver ON, uma SELECT instrução que usa WHERE column_name = NULL retorna zero linhas mesmo se houver valores NULL em column_name. Uma SELECT instrução que usa WHERE column_name <> NULL retorna zero linhas mesmo se houver valores não NULL no column_name.

Quando ANSI_NULLS está OFF, os operadores de comparação Igual a (=) e Diferente de (<>) não seguem o padrão ISO. Uma SELECT instrução que usa WHERE column_name = NULL retorna as linhas que têm valores nulos em column_name. Uma SELECT instrução que usa WHERE column_name <> NULL retorna as linhas que não têmNULL valores na coluna. Além disso, uma SELECT instrução que usa WHERE column_name <> XYZ_value retorna todas as linhas que não são XYZ_value e que não NULLsão .

Quando ANSI_NULLS estiver ON, todas as comparações em relação a um valor nulo serão avaliadas como UNKNOWN. Quando SET ANSI_NULLS estiver OFF, as comparações de todos os dados em relação a um valor nulo serão avaliadas como TRUE se o valor dos dados for NULL. Se SET ANSI_NULLS não for especificado, a configuração da opção ANSI_NULLS do banco de dados atual será aplicada. Para obter mais informações sobre a opção ANSI_NULLS de banco de dados, consulte ALTER DATABASE (Transact-SQL).

A tabela a seguir mostra como a configuração afeta ANSI_NULLS os resultados de expressões boolianas usando valores nulos e não nulos.

Expressão booliana	ATIVAR ANSI_NULLS	DEFINIR ANSI_NULLS DESLIGADO
`NULL = NULL`	DESCONHECIDO	VERDADEIRO
`1 = NULL`	DESCONHECIDO	FALSO
`NULL <> NULL`	DESCONHECIDO	FALSO
`1 <> NULL`	DESCONHECIDO	VERDADEIRO
`NULL > NULL`	DESCONHECIDO	DESCONHECIDO
`1 > NULL`	DESCONHECIDO	DESCONHECIDO
`NULL IS NULL`	VERDADEIRO	VERDADEIRO
`1 IS NULL`	FALSO	FALSO
`NULL IS NOT NULL`	FALSO	FALSO
`1 IS NOT NULL`	VERDADEIRO	VERDADEIRO

SET ANSI_NULLS ON afeta uma comparação somente se um dos operandos da comparação for uma variável ou NULL literal NULL. Se 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 o esperado, independentemente da opção ANSI_NULLS de banco de dados ou da configuração de SET ANSI_NULLS, use IS NULL e IS NOT NULL em comparações que possam conter valores nulos.

ANSI_NULLS deve ser definido como ON para a execução de consultas distribuídas.

ANSI_NULLS também deve estar ON quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Se SET ANSI_NULLS for OFF, quaisquer CREATE, UPDATEINSERTe DELETE instruções em tabelas com índices em colunas computadas ou exibições indexadas falharão. O SQL Server retorna um erro que lista todas as opções de SET que violam os valores necessários. Além disso, quando você executa uma SELECT instrução, se SET ANSI_NULLS estiver OFF, o SQL Server ignorará os valores de índice em colunas ou exibições computadas e resolverá a operação de seleção como se não houvesse esses índices nas tabelas ou exibições.

Observação

ANSI_NULLS é uma das sete opções SET que devem ser definidas como valores necessários 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.

O driver ODBC do SQL Server Native Client e o Provedor OLE DB do SQL Server Native Client para SQL Server são definidos ANSI_NULLS automaticamente como ON ao se conectar. 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 é SET ANSI_NULLS OFF.

Quando ANSI_DEFAULTS está ON, ANSI_NULLS está habilitado.

A configuração de é definida em tempo de execução ou execução e não em tempo de ANSI_NULLS análise.

Para exibir a configuração atual dessa configuração, execute a seguinte consulta:

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';  
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';  
SELECT @ANSI_NULLS AS ANSI_NULLS;

Permissões

Requer associação à função pública .

Exemplos

O exemplo a seguir usa os operadores de comparação Equals (=) e Not Equal To (<>) para fazer comparações com NULL ou 0 e o null valor em uma variável.

SET ANSI_NULLS OFF
DECLARE @var INT = NULL
SELECT
    IIF(@var = NULL,  'True', 'False') as EqualNull,
    IIF(@var <> NULL, 'True', 'False') as DifferentNull,
    IIF(@var = 0,     'True', 'False') as EqualZero,
    IIF(@var <> 0,    'True', 'False') as DifferentZero

Os resultados são mostrados na tabela a seguir.

EqualNull	DiferenteNulo	EqualZero	DiferenteZero
Verdade	Falso	Falso	Verdade

Com SET ANSI_NULLS ON todas as expressões seria avaliado como 'False' porque NULL não pode ser comparado com NULL ou 0 usando esses operadores.

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 a SET ANSI_NULLS configuração não afeta IS NULL.

-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO

Agora defina ANSI_NULLS como ON e teste.

PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO

Agora defina ANSI_NULLS como OFF e teste.

PRINT 'Testing ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;

Compartilhar via

CONJUNTO ANSI_NULLS (Transact-SQL)

Sintaxe

Sintaxe para SQL Server, Pool de SQL sem servidor no Azure Synapse Analytics, Microsoft Fabric

Sintaxe para Azure Synapse Analytics e PDW (Analytics Platform System)

Comentários

Permissões

Exemplos

Conteúdo relacionado

Comentários

Recursos adicionais