SET ANSI_NULLS (Transact-SQL)
Especifica o comportamento compatível ISO dos operadores de comparação Igual a (=) e Diferente de (<>) quando usados com valores nulos.
.gif "Observação importante") Importante |
|---|
Em uma versão futura do SQL Server, ANSI_NULLS sempre estará ON e quaisquer 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. |
.gif "Ícone de vínculo de tópico")
Sintaxe
SET ANSI_NULLS { ON | OFF }
Comentários
Quando SET ANSI_NULLS é definido como ON, uma instrução SELECT que usa WHERE column_name = NULL retorna zero linhas mesmo que haja valores nulos em column_name. Uma instrução SELECT que usa WHERE column_name <> NULL retorna zero linhas mesmo que haja valores não nulos em column_name.
Quando SET ANSI_NULLS for OFF, os operadores de comparação Igual a (=) e Diferente de (<>) 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 nulos 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 nem NULL.
Quando SET 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, consulte ALTER DATABASE (Transact-SQL) e Definindo opções do banco de dados.
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.
SET ANSI_NULLS deve ser definido como ON para executar consultas distribuídas.
SET 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 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.
.gif "Observação") 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 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.
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.
Permissões
Requer associação na 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 SET ANSI_NULLS.
-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO
-- Print message and perform SELECT statements.
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
-- SET ANSI_NULLS to ON and test.
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
-- SET ANSI_NULLS to OFF and test.
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
-- Drop table t1.
DROP TABLE t1