SET ANSI_NULLS (Transact-SQL)SET ANSI_NULLS (Transact-SQL)

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Especifica o comportamento compatível com ISO dos operadores de comparação Igual a (=) e Diferente de (<>) quando usados com valores nulos na SQL ServerSQL Server.Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL ServerSQL Server.

Importante

Em uma versão futura do SQL ServerSQL Server, ANSI_NULLS estará ON e quaisquer aplicativos que definam explicitamente a opção como OFF gerarão um erro.In a future version of SQL ServerSQL Server, ANSI_NULLS will be ON and any applications that explicitly set the option to OFF will generate an error. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

-- Syntax for SQL Server

SET ANSI_NULLS { ON | OFF }
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

SET ANSI_NULLS ON

RemarksRemarks

Quando ANSI_NULLS é definido como ON, uma instrução SELECT que usa WHERE column_name = NULL retornará zero linha, mesmo que haja valores nulos em column_name.When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. Uma instrução SELECT usando WHERE column_name <> NULL retorna zero linha mesmo que haja valores não nulos em column_name.A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

Quando ANSI_NULLS for OFF, os operadores de comparação Igual a (=) e Não Igual a (<>) não seguem o padrão ISO.When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. Uma instrução SELECT que usa WHERE column_name = NULL retorna as linhas que têm valores nulos em column_name.A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. Uma instrução SELECT que usa WHERE column_name <> NULL retorna as linhas que têm valores não nulos na coluna.A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. 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 nulas.Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

Quando ANSI_NULLS for ON, todas as comparações em relação a um valor nulo serão avaliadas como UNKNOWN.When ANSI_NULLS is ON, all comparisons against a null value evaluate to 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.When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. Se SET ANSI_NULLS não for especificado, a configuração da opção ANSI_NULLS do banco de dados atual será aplicada.If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies. Para obter mais informações sobre a opção de banco de dados ANSI_NULLS, veja ALTER DATABASE (Transact-SQL).For more information about the ANSI_NULLS database option, see 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.The following table shows how the setting of ANSI_NULLS affects the results of a number of Boolean expressions using null and non-null values.

Expressão boolianaBoolean Expression SET ANSI_NULLS ONSET ANSI_NULLS ON SET ANSI_NULLS OFFSET ANSI_NULLS OFF
NULL = NULLNULL = NULL UNKNOWNUNKNOWN TRUETRUE
1 = NULL1 = NULL UNKNOWNUNKNOWN FALSEFALSE
NULL <> NULLNULL <> NULL UNKNOWNUNKNOWN FALSEFALSE
1 <> NULL1 <> NULL UNKNOWNUNKNOWN TRUETRUE
NULL > NULLNULL > NULL UNKNOWNUNKNOWN UNKNOWNUNKNOWN
1 > NULL1 > NULL UNKNOWNUNKNOWN UNKNOWNUNKNOWN
NULL IS NULLNULL IS NULL TRUETRUE TRUETRUE
1 IS NULL1 IS NULL FALSEFALSE FALSEFALSE
NULL IS NOT NULLNULL IS NOT NULL FALSEFALSE FALSEFALSE
1 IS NOT NULL1 IS NOT NULL TRUETRUE TRUETRUE

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.SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. Se os dois lados da comparação forem colunas ou expressões compostas, a configuração não afetará a comparação.If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

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.For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

ANSI_NULLS deve ser definido como ON para executar consultas distribuídas.ANSI_NULLS should be set to ON for executing distributed queries.

ANSI_NULLS também deve ser ON quando você estiver criando ou alterando índices em colunas computadas ou exibições indexadas.ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. 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.If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. O SQL ServerSQL Server retorna um erro que lista todas as opções de SET que violam os valores necessários.SQL ServerSQL Server returns an error that lists all SET options that violate the required values. Além disso, ao executar uma instrução SELECT, se SET ANSI_NULLS for OFF, o SQL ServerSQL 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.Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL ServerSQL Server ignores the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

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.ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. 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.The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF.

O driver ODBC do SQL ServerSQL Server Native Client e o Provedor OLE DB Provider do SQL ServerSQL Server Native Client para SQL ServerSQL Server definem automaticamente ANSI_NULLS como ON durante a conexão.The SQL ServerSQL Server Native Client ODBC driver and SQL ServerSQL Server Native Client OLE DB Provider for SQL ServerSQL Server automatically set ANSI_NULLS to ON when connecting. 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 ServerSQL Server.This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to an instance of SQL ServerSQL Server. O padrão para SET ANSI_NULLS é OFF.The default for SET ANSI_NULLS is OFF.

Quando ANSI_DEFAULTS é ON, ANSI_NULLS está habilitado.When ANSI_DEFAULTS is ON, ANSI_NULLS is enabled.

A configuração de ANSI_NULLS é definida no momento da execução, e não no momento da análise.The setting of ANSI_NULLS is defined at execute or run time and not at parse time.

Para exibir a configuração atual dessa configuração, execute a seguinte consulta:To view the current setting for this setting, run the following query:

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

PermissõesPermissions

Requer associação à função pública .Requires membership in the public role.

ExemplosExamples

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.The following example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL and non-null values in a table. O exemplo também mostra que IS NULL não é afetado pela configuração de SET ANSI_NULLS.The example also shows that IS NULL is not affected by the SET ANSI_NULLS setting.

-- 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.Now set ANSI_NULLS to ON and test.

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.Now set ANSI_NULLS to OFF and test.

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;  

Consulte TambémSee Also

Instruções SET (Transact-SQL) SET Statements (Transact-SQL)
SESSIONPROPERTY (Transact-SQL) SESSIONPROPERTY (Transact-SQL)
= (Equals) (Transact-SQL) = (Equals) (Transact-SQL)
IF...ELSE (Transact-SQL) IF...ELSE (Transact-SQL)
<> (Not Equal To) (Transact-SQL) <> (Not Equal To) (Transact-SQL)
Instruções SET (Transact-SQL) SET Statements (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL) SET ANSI_DEFAULTS (Transact-SQL)
WHERE (Transact-SQL) WHERE (Transact-SQL)
WHILE (Transact-SQL)WHILE (Transact-SQL)