CONJUNTO ANSI_NULLS (Transact-SQL)

Aplica-se a:Banco de Dados SQL doAzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric

Especifica o comportamento compatível com ISO dos operadores de comparação Igual (=) e Não Igual a (<>) 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 de {expression} fosse NULL. Esse comportamento é compatível com ANSI.
• SET ANSI_NULLS OFF- Avalia como e como se o valor de {expression} = NULL é True.{expression} <> NULLFalse{expression}NULL Esse comportamento não é recomendado, porque os NULL valores não devem ser comparados usando = e <> operadores.

Observação

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

Transact-SQL convenções de sintaxe

Sintaxe

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

SET ANSI_NULLS { ON | OFF }

Sintaxe do Azure Synapse Analytics and Analytics Platform System (PDW)

SET ANSI_NULLS ON

Observações

Quando ANSI_NULLS está 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 linhas zero mesmo se houver valores não-NULL em column_name.

Quando ANSI_NULLS está DESATIVADO, os operadores de comparação Igual (=) e Não Igual a (<>) 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 está ON, todas as comparações com um valor nulo são avaliadas como UNKNOWN. Quando SET ANSI_NULLS é OFF, as comparações de todos os dados com um valor nulo são avaliadas como TRUE se o valor dos dados for NULL. Se SET ANSI_NULLS não for especificado, aplica-se a ANSI_NULLS configuração da opção do banco de dados atual. Para obter mais informações sobre a ANSI_NULLS opção de banco de dados, consulte ALTER DATABASE (Transact-SQL).

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

Expressão Booleana	DEFINIR ANSI_NULLS EM	INICIAR ANSI_NULLS
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 um literal NULL. Se ambos os lados da comparação forem colunas ou expressões compostas, a configuração não afetará a comparação.

Para que um script funcione como pretendido, independentemente da opção de banco de dados ou da ANSI_NULLS 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 executar consultas distribuídas.

ANSI_NULLS também deve estar ATIVADO quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Se SET ANSI_NULLS estiver OFF, qualquer CREATEinstrução , UPDATEINSERT, e DELETE em tabelas com índices em colunas computadas ou exibições indexadas falhará. O SQL Server retorna um erro que lista todas as opções 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 ignora os valores de índice em colunas ou exibições computadas e resolve a operação select como se não houvesse tais í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 devem ser definidas 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 ANSI_NULLS definidos automaticamente como ON ao se conectar. Essa configuração pode ser definida em fontes de dados ODBC, em atributos de conexão ODBC ou em propriedades de conexão OLE DB definidas no aplicativo antes de se conectar a uma instância do SQL Server. O padrão para SET ANSI_NULLS é OFF.

Quando ANSI_DEFAULTS está ATIVADO, ANSI_NULLS está ativado.

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 para essa 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 adesão à função pública de .

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 apresentados na tabela seguinte.

EqualNull	DifferentNull	EqualZero	DiferenteZero
Verdade	Falso	Falso	Verdade

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

O exemplo a seguir usa os operadores de comparação Equals (=) e Not Equal To (<>) para fazer comparações com NULL e valores não nulos em uma tabela. O exemplo também mostra que a configuração não afeta SET ANSI_NULLSo 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 para 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 para 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;  

Conteúdo relacionado

• Instruções SET (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (Igual) (Transact-SQL)
• Se... ELSE (Transact-SQL)
• <> (Não igual a) (Transact-SQL)
• CONJUNTO ANSI_DEFAULTS (Transact-SQL)
• ONDE (Transact-SQL)
• ENQUANTO (Transact-SQL)