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

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.

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.

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.

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)