CONJUNTO ANSI_NULLS (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Especifica el comportamiento compatible con ISO de los operadores de comparación Equals (=) y Not Equal To (<>) cuando se usan con NULL valores en SQL Server.

• SET ANSI_NULLS ON : evalúa y {expression} = NULL{expression} <> NULL como False si el valor de {expression} es NULL. Este comportamiento es compatible con ANSI.
• SET ANSI_NULLS OFF- Evalúa como y como si el valor de {expression} = NULL es True.{expression} <> NULLFalse{expression}NULL Este comportamiento no se recomienda, ya que los NULL valores no deben compararse mediante = operadores y <> .

Nota:

SET ANSI_NULLS OFF y la ANSI_NULLS OFF opción de base de datos están en desuso. A partir de SQL Server 2017 (14.x), ANSI_NULLS siempre se establece en ACTIVADO. Las características en desuso no se deben usar en nuevas aplicaciones. Para obtener más información, vea Características de Motor de base de datos en desuso en SQL Server 2017.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis de SQL Server y un grupo de SQL sin servidor en Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Sintaxis para Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

SET ANSI_NULLS ON

Comentarios

Cuando ANSI_NULLS es ON, una SELECT instrucción que usa WHERE column_name = NULL devuelve cero filas incluso si hay valores NULL en column_name. Una SELECT instrucción que usa WHERE column_name <> NULL devuelve cero filas incluso si hay valores que no son NULL en column_name.

Cuando ANSI_NULLS es OFF, los operadores de comparación Equals (=) y Not Equal To (<>) no siguen el estándar ISO. Una SELECT instrucción que usa WHERE column_name = NULL devuelve las filas que tienen valores NULL en column_name. Una SELECT instrucción que usa WHERE column_name <> NULL devuelve las filas que tienen valores que noNULL son de la columna. Además, una SELECT instrucción que usa WHERE column_name <> XYZ_value devuelve todas las filas que no están XYZ_value y que no NULLson .

Cuando ANSI_NULLS es ON, todas las comparaciones con un valor NULL se evalúan como UNKNOWN. Cuando SET ANSI_NULLS es OFF, las comparaciones de todos los datos con un valor NULL se evalúan como TRUE si el valor de los datos es NULL. Si SET ANSI_NULLS no se especifica, se aplica el valor de la ANSI_NULLS opción de la base de datos actual. Para obtener más información sobre la ANSI_NULLS opción de base de datos, vea ALTER DATABASE (Transact-SQL).

En la tabla siguiente se muestra cómo la configuración de ANSI_NULLS afecta a los resultados de las expresiones booleanas mediante valores NULL y no NULL.

Expresión booleana	PONER ANSI_NULLS ENCENDIDO	DESCONECTAR ANSI_NULLS
NULL = NULL	DESCONOCIDO	true
1 = NULL	DESCONOCIDO	FALSO
NULL <> NULL	DESCONOCIDO	FALSO
1 <> NULL	DESCONOCIDO	true
NULL > NULL	DESCONOCIDO	DESCONOCIDO
1 > NULL	DESCONOCIDO	DESCONOCIDO
NULL IS NULL	true	true
1 IS NULL	FALSO	FALSO
NULL IS NOT NULL	FALSO	FALSO
1 IS NOT NULL	true	true

SET ANSI_NULLS ON afecta a una comparación solo si uno de los operandos de la comparación es una variable que es NULL o un literal NULL. Si ambos lados de la comparación son columnas o expresiones compuestas, la configuración no afecta a la comparación.

Para que un script funcione según lo previsto, independientemente de la ANSI_NULLS opción de base de datos o del valor de SET ANSI_NULLS, use IS NULL y IS NOT NULL en comparaciones que puedan contener valores NULL.

ANSI_NULLS debe establecerse en ON para ejecutar consultas distribuidas.

ANSI_NULLS también debe estar activado al crear o cambiar índices en columnas calculadas o vistas indizadas. Si SET ANSI_NULLS es OFF, se produce un error en las CREATEinstrucciones , UPDATE, INSERTy DELETE en tablas con índices en columnas calculadas o vistas indizadas. SQL Server devuelve un error que muestra todas las opciones SET que infringen los valores necesarios. Además, al ejecutar una SELECT instrucción, si SET ANSI_NULLS es OFF, SQL Server omite los valores de índice en las columnas o vistas calculadas y resuelve la operación de selección como si no hubiera dichos índices en las tablas o vistas.

Nota:

ANSI_NULLS es una de las siete opciones SET que se deben establecer en valores necesarios al tratar con índices en columnas calculadas o vistas indizadas. Las opciones ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, y CONCAT_NULL_YIELDS_NULL también se deben establecer en ON, y NUMERIC_ROUNDABORT en OFF.

El controlador ODBC de SQL Server Native Client y el proveedor OLE DB de SQL Server Native Client para SQL Server se establecen ANSI_NULLS automáticamente en ON al conectarse. Esta opción se puede configurar en los orígenes de datos ODBC, en los atributos de conexión ODBC o en las propiedades de conexión OLE DB establecidas en la aplicación antes de conectarse a una instancia de SQL Server. El valor predeterminado de SET ANSI_NULLS es OFF.

Cuando ANSI_DEFAULTS está activado, ANSI_NULLS está habilitado.

La configuración de se define en tiempo de ANSI_NULLS ejecución o ejecución y no en tiempo de análisis.

Para ver la configuración actual de este valor, ejecute la siguiente consulta:

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

Permisos

Debe pertenecer al rol public .

Ejemplos

En el ejemplo siguiente se usan los operadores de comparación Equals (=) y Not Equal To (<>) para realizar comparaciones con NULL o 0 y el null valor de una variable.

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

Los resultados se muestran en la tabla siguiente.

EqualNull	DifferentNull	EqualZero (Igual a Cero)	DifferentZero (DiferenteCero)
Cierto	Falso	Falso	Cierto

Con SET ANSI_NULLS ON todas las expresiones se evaluaría como "False" porque NULL no se puede comparar con NULL estos operadores ni 0 usarlos.

En el ejemplo siguiente se usan los operadores de comparación Es igual a (=) y No es igual a (<>) para realizar comparaciones con valores NULL y distintos de NULL en una tabla. En el ejemplo también se muestra que la SET ANSI_NULLS configuración no afecta a 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 

Ahora, establezca ANSI_NULLS en ON y pruebe.

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  

Ahora, establezca ANSI_NULLS en OFF y pruebe.

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;  

Contenido relacionado

• Instrucciones SET (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (Igual a) (Transact-SQL)
• SI... ELSE (Transact-SQL)
• <> (No es igual a) (Transact-SQL)
• CONJUNTO ANSI_DEFAULTS (Transact-SQL)
• DONDE (Transact-SQL)
• MIENTRAS (Transact-SQL)