SET ANSI_NULLS (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Especifica o comportamento compatível com ISO dos operadores de comparação Igual (=) e Não Igual a (<>) quando eles são usados com valores nulos no SQL Server.

Observação

SET ANSI_NULLS OFF e a opção de banco de dados ANSI_NULLS OFF foram preteridas. 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

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Comentários

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

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

Quando ANSI_NULLS for ON, todas as comparações em relação a um valor nulo serão avaliadas como UNKNOWN. Quando SET ANSI_NULLS for OFF, as comparações de todos os dados em relação a um valor nulo serão avaliadas como TRUE. 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 de banco de dados ANSI_NULLS, veja ALTER DATABASE (Transact-SQL).

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

Expressão booliana COLOCAR ANSI_NULLS EM ABERTO SET ANSI_NULLS OFF
NULL = NULL UNKNOWN TRUE
1 = NULL UNKNOWN FALSE
NULL <> NULL UNKNOWN FALSE
1 <> NULL UNKNOWN TRUE
NULL > NULL DESCONHECIDO UNKNOWN
1 > NULL UNKNOWN UNKNOWN
NULL IS NULL TRUE TRUE
1 IS NULL FALSE FALSE
NULL IS NOT NULL FALSE FALSE
1 IS NOT NULL TRUE TRUE

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

ANSI_NULLS deve ser definido como ON para executar consultas distribuídas.

ANSI_NULLS também deve ser ON quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas. Se SET ANSI_NULLS estiver OFF, haverá falha em qualquer instrução CREATE, UPDATE, INSERT e DELETE das tabelas com índices em colunas computadas ou exibições indexadas. O SQL Server retorna um erro que lista todas as opções de SET que violam os valores necessários. Além disso, ao executar uma instrução SELECT, se SET ANSI_NULLS for OFF, o SQL Server ignorará os valores de índice nas exibições ou colunas computadas e resolverá a operação selecionada 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 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.

O driver ODBC do SQL Server Native Client e o Provedor OLE DB Provider do SQL Server Native Client para SQL Server 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.

Quando ANSI_DEFAULTS é ON, ANSI_NULLS está habilitado.

A configuração de ANSI_NULLS é definida no momento da execução, e não no momento da 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 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 de SET ANSI_NULLS.

-- 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;

Conteúdo relacionado